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

    <select id="getJyjlList" resultType="com.zjrs.ggfw.rlzysc.dto.JlxxJyjlUDTO">
     select a.rs_rlzy_jyjl_dyb_lsh dyblsh,b.rs_rlzy_jyjl_lsh as "rsrlzyjyjllsh",b.sfyhwjl as "sfyhwjl",b.byyx as "byyx",
     b.rxsj as "rxsj",b.bysj as "bysj",b.xl as "xl",b.sxzy as "sxzy",b.xw as "xw",b.xz as "xz"
     from v_rs_rlzy_jyjl_dyb a,v_rs_rlzy_jyjl b where
     a.rs_rlzy_jlxx_lsh = #{dto.rsrlzyjlxxlsh} and a.rs_rlzy_jyjl_lsh = b.rs_rlzy_jyjl_lsh
        order by b.ts_xgsj desc
    </select>


    <select id="getJyjlpoList" parameterType="com.zjrs.ggfw.rlzysc.dto.grzxjlxx.ZjDTO" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyJyjlPO">
        select b.*,
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC011' and aaa102= b.xl) as "xlname",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_XW' and aaa102= b.xw) as "xwname",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_XZ' and aaa102= b.xz) as "xzname",
        a.rs_rlzy_jyjl_dyb_lsh as dyblsh
        from v_rs_rlzy_jyjl_dyb a,v_rs_rlzy_jyjl b where
        a.rs_rlzy_jlxx_lsh = #{dto.zjlsh}
        order by b.ts_xgsj desc
    </select>

    <select id="getJyjlNrById" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyJyjlPO">
         select * from v_rs_rlzy_jyjl c where c.rs_rlzy_jyjl_lsh not in
       (select a.rs_rlzy_jyjl_lsh from v_rs_rlzy_jyjl_dyb a,v_rs_rlzy_jlxx b
       where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh and a.rs_rlzy_jlxx_lsh = #{jlxxlsh}
       and b.rs_yhlsh = #{yhlsh}) and c.rs_yhlsh = #{yhlsh} 
    </select>

    <select id="getjyjlWhList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyJyjlPO">
         select * from v_rs_rlzy_jyjl c where c.rs_rlzy_jyjl_lsh not in
       (select a.rs_rlzy_jyjl_lsh from v_rs_rlzy_jyjl_dyb a,v_rs_rlzy_jlxx b
       where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh and a.rs_rlzy_jlxx_lsh =  #{dto.rsrlzyjlxxlsh}
       and b.rs_yhlsh = #{yhlsh}
        )
        and c.rs_yhlsh = #{yhlsh} order by c.ts_xgsj desc
    </select>

</mapper>
