<?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.GzjyMapper">
    <select id="getgzjyList" resultType="com.zjrs.ggfw.rlzysc.dto.JlxxGzjyUDTO">
     select a.rs_rlzy_gzjy_dyb_lsh as "dyblsh",b.rs_rlzy_gzjy_lsh as "rsrlzygzjylsh",b.GZDW as "gzdw",
     b.SZZW as "szzw",b.KSSJ as "kssj",b.JSSJ as "jssj",b.SSHY as "sshy",b.DWLB as "dwlb",
     b.JJLX as "jjlx",b.RYGM as "rygm",b.GZZZ as "gzzz",b.GZYJ as "gzyj"
     from v_rs_rlzy_gzjy_dyb a,v_rs_rlzy_gzjy b where
     a.rs_rlzy_jlxx_lsh = #{dto.rsrlzyjlxxlsh}
     and b.ts_yxbz = '1'order by b.ts_xgsj desc
    </select>


    <select id="getgzjypoList" parameterType="com.zjrs.ggfw.rlzysc.dto.grzxjlxx.ZjDTO" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyGzjyPO">
        select b.rs_rlzy_gzjy_lsh,b.rs_yhlsh,b.gzdw,b.szzw,b.kssj,nvl(b.jssj, '至今')  as "jssj",b.sshy,b.dwlb,b.jjlx,b.rygm,b.gzzz,b.gzyj,b.ts_cjr,b.ts_cjsj,b.ts_xgr,b.ts_xgsj,b.ts_yxbz,
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_SSHY' and aaa102= b.sshy) as "sshyname",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAB019' and aaa102= b.dwlb) as "dwlbname",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_JJLX' and aaa102= b.jjlx) as "jjlxname",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_RYGM' and aaa102= b.rygm) as "rygmname",
        a.rs_rlzy_gzjy_dyb_lsh as dyblsh
        from v_rs_rlzy_gzjy_dyb a, v_rs_rlzy_gzjy b
        where a.rs_rlzy_jlxx_lsh = #{dto.zjlsh}
        order by b.ts_xgsj desc
    </select>

    <insert id="insertGzjy" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="vrsrlzyzwwzglpo">
            insert into v_rs_rlzy_gzjy
            (rs_rlzy_gzjy_lsh, rs_yhlsh, gzdw, szzw, kssj, jssj,sshy,dwlb,jjlx,rygm,gzzz,gzyj,ts_cjr,ts_cjsj,ts_xgr,ts_xgsj,ts_yxbz)
            values
            (
            #{vrsrlzyzwwzglpo.rsrlzyzwwzgllsh},
            #{vrsrlzyzwwzglpo.rsrlzyzwgllsh},
            #{vrsrlzyzwwzglpo.hsh},
            #{vrsrlzyzwwzglpo.lsh},
            '01',
            #{vrsrlzyzwwzglpo.zwwzmc}
            );
        </foreach>
        end;
    </insert>

    <select id="getGzjyNrById" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyGzjyPO">
       select * from v_rs_rlzy_gzjy c where c.rs_rlzy_gzjy_lsh not in
       (select a.rs_rlzy_gzjy_lsh from v_rs_rlzy_gzjy_dyb a,v_rs_rlzy_jlxx b
       where a.rs_rlzy_jlxx_lsh = #{jlxxlsh}
       and b.rs_yhlsh = #{yhlsh}) and c.rs_yhlsh = #{yhlsh}
    </select>

    <select id="getgzjyWhList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyGzjyPO">
        select * from v_rs_rlzy_gzjy c where c.rs_rlzy_gzjy_lsh not in
       (select a.rs_rlzy_gzjy_lsh from v_rs_rlzy_gzjy_dyb a,v_rs_rlzy_jlxx b
       where 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>
