<?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.sxgl.entity.SxtjMapper">

    <select id="querySxtjList" parameterType="com.zjrs.zwnw.sxgl.dto.SxtjQueryDTO" resultType="com.zjrs.zwnw.sxgl.dto.SxtjListDTO">
        select a.sxbm, a.jbbm, a.sxmc, a.blsx,a.cnbjsxdw,a.dxccs,a.sxlx,a.sxlb,a.ssbmmc,
        NVL(b.zblywl ,0) zblywl, NVL(b.bndywl ,0) bndywl,NVL(b.byywl ,0) byywl
        from rs_sx_config a left join
        (SELECT sbsx,
            COUNT(1) zblywl,
            COUNT(CASE WHEN (SUBSTR(replace(cjsj,'-',''), 2, 4)=#{ dto.bn })  THEN 1 ELSE null END) bndywl,
            COUNT(CASE WHEN (SUBSTR(replace(cjsj,'-',''), 3, 6)=#{ dto.by }) THEN 1 ELSE null END) byywl
        FROM v_rs_slxx_wbbj where 1=1
            <if test="dto.sjly != null and dto.sjly != ''">
                and sjly = #{ dto.sjly }
            </if>
        group by sbsx ) b on b.sbsx = a.sxbm
        where 
            <if test="dto.sxlx != null and dto.sxlx != ''">
                a.sxlx = #{ dto.sxlx }
            </if>
        order by zblywl DESC, sxbm DESC
    </select>

    <select id="queryYwltjList" parameterType="com.zjrs.zwnw.sxgl.dto.YwltjQueryDTO" resultType="com.zjrs.zwnw.sxgl.dto.YwltjResultDTO">
        SELECT * FROM (SELECT '柜台经办' "sqqdname", '1' "sqqd",sxlb "sxlb",
        (select sxmc from rs_sx_config a where a.sxbm = q.sbsx) "sxmc",sbsx "sbsx", count(1) "ywl",
        (SELECT aaa103 FROM V_GGFW_DICT WHERE AAA100 = 'SX_SXLB' and aaa102 = sxlb) "zbks",
        to_char(sum(fcbmy)) "fcbmy",to_char(sum(bmy)) "bmy",to_char(sum(jbmy)) "jbmy",
        to_char(sum(my)) "my",to_char(sum(fcmy)) "fcmy",
        decode(instr(to_char(ROUND(((sum(fcbmy)+sum(bmy))/count(1))*100,1),'fm9999990.0'),'.'),'0',
        to_char(ROUND(((sum(fcbmy)+sum(bmy))/count(1))*100,1),'fm9999990.0'),
        to_char(ROUND(((sum(fcbmy)+sum(bmy))/count(1))*100,1),'fm9999990.0'))||'%' "cpl",
        decode(instr(to_char(ROUND(((sum(jbmy)+sum(my)+sum(fcmy))/count(1))*100,1),'fm9999990.0'),'.'),'0',
        to_char(ROUND(((sum(jbmy)+sum(my)+sum(fcmy))/count(1))*100,1),'fm9999990.0'),
        to_char(ROUND(((sum(jbmy)+sum(my)+sum(fcmy))/count(1))*100,1),'fm9999990.0'))||'%' "hpl"
        FROM (select
        sbsx,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '1') fcbmy,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '2') bmy,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '3') jbmy,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '4') my,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '5') fcmy,
        (select sxlb from rs_sx_config a where a.sxbm = b.sbsx) sxlb
        from rs_slxx b
        where 
        <if test="dto.sqrqc != null and dto.sqrqc != ''">
             to_date(substr(slbh,0,8),'yyyyMMdd') &gt;= to_date(REPLACE(#{dto.sqrqc},'-',''),'yyyyMMdd')
        </if>
        ) q group by sbsx,sxlb        
        ) where "sxmc" IS NOT NULL
        <if test="dto.sqqd != null and dto.sqqd != ''">
            and "sqqd" = #{dto.sqqd}
        </if>
        <if test="dto.sxmc != null and dto.sxmc != ''">
            and "sxmc" like '%'||#{dto.sxmc}||'%'
        </if>
    </select>


    <select id="exportYwltjList" parameterType="com.zjrs.zwnw.sxgl.dto.YwltjQueryDTO" resultType="com.zjrs.zwnw.sxgl.dto.YwltjResultDTO">
        SELECT "sqqdname","sxmc","ywl","zbks","fcmy","my","jbmy","hpl","bmy","fcbmy","cpl"
        FROM (SELECT '柜台经办' "sqqdname", '1' "sqqd",sxlb "sxlb",
        (select sxmc from rs_sx_config a where a.sxbm = q.sbsx) "sxmc",sbsx "sbsx", count(1) "ywl",
        (SELECT aaa103 FROM V_GGFW_DICT WHERE AAA100 = 'SX_SXLB' and aaa102 = sxlb) "zbks",
        to_char(sum(fcbmy)) "fcbmy",to_char(sum(bmy)) "bmy",to_char(sum(jbmy)) "jbmy",
        to_char(sum(my)) "my",to_char(sum(fcmy)) "fcmy",
        decode(instr(to_char(ROUND(((sum(fcbmy)+sum(bmy))/count(1))*100,1),'fm9999990.0'),'.'),'0',
        to_char(ROUND(((sum(fcbmy)+sum(bmy))/count(1))*100,1),'fm9999990.0'),
        to_char(ROUND(((sum(fcbmy)+sum(bmy))/count(1))*100,1),'fm9999990.0'))||'%' "cpl",
        decode(instr(to_char(ROUND(((sum(jbmy)+sum(my)+sum(fcmy))/count(1))*100,1),'fm9999990.0'),'.'),'0',
        to_char(ROUND(((sum(jbmy)+sum(my)+sum(fcmy))/count(1))*100,1),'fm9999990.0'),
        to_char(ROUND(((sum(jbmy)+sum(my)+sum(fcmy))/count(1))*100,1),'fm9999990.0'))||'%' "hpl"
        FROM (select
        sbsx,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '1') fcbmy,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '2') bmy,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '3') jbmy,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '4') my,
        (SELECT count(1) FROM RS_JK_HCP WHERE sllsh = b.sllsh AND EVALPROCESS='2' AND yhpj = '5') fcmy,
        (select sxlb from rs_sx_config a where a.sxbm = b.sbsx) sxlb
        from rs_slxx b
        where sjly = '1'
        <if test="dto.sqrqc != null and dto.sqrqc != ''">
            AND to_date(substr(slbh,0,8),'yyyyMMdd') &gt;= to_date(REPLACE(#{dto.sqrqc},'-',''),'yyyyMMdd')
        </if>
        <if test="dto.sqrqz != null and dto.sqrqz != ''">
            AND to_date(substr(slbh,0,8),'yyyyMMdd') &lt;= to_date(REPLACE(#{dto.sqrqz},'-',''),'yyyyMMdd')
        </if>
        ) q group by sbsx,sxlb
        ) where "sxmc" IS NOT NULL
        <if test="dto.sqqd != null and dto.sqqd != ''">
            and "sqqd" = #{dto.sqqd}
        </if>
        <if test="dto.sxmc != null and dto.sxmc != ''">
            and "sxmc" like '%'||#{dto.sxmc}||'%'
        </if>
    </select>
</mapper>
