<?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.HjqkMapper">
    <select id="getHjqkList" resultType="com.zjrs.ggfw.rlzysc.dto.JlxxHjqkUDTO">
    select a.rs_rlzy_hjqk_dyb_lsh dyblsh, b.rs_rlzy_hjqk_lsh rsrlzyhjqklsh,b.ZSMC zsmc,b.HQSJ hqsj,b.BJJG bjjg,b.JS js,b.CGMC cgmc
    ,b.CGSM cgsm,b.QTSM qtsm from v_rs_rlzy_hjqk_dyb a,v_rs_rlzy_hjqk b where
     a.rs_rlzy_jlxx_lsh = #{dto.rsrlzyjlxxlsh}
     and a.rs_rlzy_hjqk_lsh = b.rs_rlzy_hjqk_lsh order by b.ts_xgsj desc
    </select>

    <select id="getHjqkpoList" parameterType="com.zjrs.ggfw.rlzysc.dto.grzxjlxx.ZjDTO" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyHjqkPO">
        select b.*,
        a.rs_rlzy_hjqk_dyb_lsh as dyblsh
        from v_rs_rlzy_hjqk_dyb a,v_rs_rlzy_hjqk b where
        a.rs_rlzy_jlxx_lsh = #{dto.zjlsh}
        order by b.ts_xgsj desc
    </select>

    <select id="getHjqkNrById" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyHjqkPO">
         select * from v_rs_rlzy_hjqk c where c.rs_rlzy_hjqk_lsh not in
       (select a.rs_rlzy_hjqk_lsh from v_rs_rlzy_hjqk_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="getHjqkWhList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyHjqkPO">
         select * from v_rs_rlzy_hjqk c where c.rs_rlzy_hjqk_lsh not in
       (select a.rs_rlzy_hjqk_lsh from v_rs_rlzy_hjqk_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>
