<?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.ggfw.rlzysc.entity.JrzphdwxxMapper">
    <select id="getQyxxList" resultType="com.zjrs.ggfw.rlzysc.dto.JrzphdwListDTO">
        select b.*,g.aab022 sshy from rs_rlzy_qyxx b, v_rs_ggfw_user f, v_ab01 g
        where b.rs_yhlsh = f.USERID
        and f.FWDX = '2' and f.ZTID = g.ZTID
        and b.rs_rlzy_qyxx_lsh in( select a.rs_rlzy_qyxx_lsh from RS_RLZY_ZWWZYYSH a where
        a.rs_rlzy_zphgl_lsh = #{dto.rsrlzyzphgllsh} 
        <if test="dto.sshy != null and dto.sshy != ''">
            and g.aab022 = #{dto.sshy}
        </if>
    </select>

    <select id="getZwxxList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZwxxPO">
        select a.* from v_rs_rlzy_zwxx a,v_rs_rlzy_qyxx b where
        a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh 
        order by a.ts_xgsj desc
    </select>

    <select id="getZxjrdZphList" resultType="com.zjrs.ggfw.rlzysc.dto.JrzphdwListDTO">
        select z.rs_rlzy_zwxx_lsh,z.zwmc,q.rs_rlzy_qyxx_lsh,q.bae005 as jrsj,q.gsmc
        from rs_rlzy_zwxx z,(select distinct c.gsmc,c.rs_rlzy_qyxx_lsh,a.bae005,c.qyxx_shzt
        from v_rs_rlzy_zwwzyysh a,v_rs_rlzy_zphgl b,v_rs_rlzy_qyxx c
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and c.rs_rlzy_qyxx_lsh = a.rs_rlzy_qyxx_lsh)q where z.rs_rlzy_qyxx_lsh = q.rs_rlzy_qyxx_lsh
        order by q.bae005 desc
    </select>

    <select id="getJrzphdwxx" resultType="com.zjrs.ggfw.rlzysc.dto.JrzphdwListDTO">
        select max(a.bae005) jrsj,a.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,c.aab004 as aab004
        from rs_rlzy_zwwzyysh a,v_rs_rlzy_zphgl b,v_rs_rlzy_qyxx c
        where a.rs_rlzy_qyxx_lsh
        = c.rs_rlzy_qyxx_lsh and b.rs_rlzy_zphgl_lsh = a.rs_rlzy_zphgl_lsh
        and a.shjg = '1' and c.qyxx_shzt = '1'
        group by a.rs_rlzy_qyxx_lsh,c.aab004 order by jrsj desc
    </select>

    <select id="getZzzwxxList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZwxxPO">
        select *
        from rs_rlzy_zwxx d
        where d.rs_rlzy_zwxx_lsh in
        (select c.rs_rlzy_zwxx_lsh
        from rs_rlzy_zwwzyyzwxx c,
        (select a.*
        from RS_RLZY_ZWWZYYSH a
        where a.rs_rlzy_zphgl_lsh = #{rsrlzyzphgllsh}) b
        where c.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and c.rs_rlzy_zwgl_lsh = b.rs_rlzy_zwgl_lsh)
    </select>
</mapper>
