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

    <select id="getZcqkpoList" parameterType="com.zjrs.ggfw.rlzysc.dto.grzxjlxx.ZjDTO" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZcqkPO">
        select b.*,
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_ZGDJ' and aaa102= b.zgdj) as "zgdjname",
        a.rs_rlzy_zcqk_dyb_lsh as dyblsh
        from v_rs_rlzy_zcqk_dyb a,v_rs_rlzy_zcqk b where
        a.rs_rlzy_jlxx_lsh = #{dto.zjlsh}
        and a.rs_rlzy_zcqk_lsh = b.rs_rlzy_zcqk_lsh
        and b.ts_yxbz = '1'
        order by b.ts_xgsj desc
    </select>

    <select id="getZcqkList"  resultType="com.zjrs.ggfw.rlzysc.dto.JlxxZcqkUDTO">
        select a.rs_rlzy_zcqk_dyb_lsh dyblsh,a.rs_rlzy_zcqk_lsh rsrlzyzcqklsh,b.zcmc,b.zcxl,b.hqsj,b.zgdj,b.pdfs,b.zcsm
        from v_rs_rlzy_zcqk_dyb a,v_rs_rlzy_zcqk b where
        a.rs_rlzy_jlxx_lsh = #{dto.rsrlzyjlxxlsh}
        and a.rs_rlzy_zcqk_lsh = b.rs_rlzy_zcqk_lsh
        order by b.ts_xgsj desc
    </select>

    <select id="getZcqkNrById" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZcqkPO">
        select * from v_rs_rlzy_zcqk c where c.rs_rlzy_zcqk_lsh not in
       (select a.rs_rlzy_zcqk_lsh from v_rs_rlzy_zcqk_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}) 
    </select>

    <select id="getZcqkWhList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZcqkPO">
        select * from v_rs_rlzy_zcqk c where c.rs_rlzy_zcqk_lsh not in
       (select a.rs_rlzy_zcqk_lsh from v_rs_rlzy_zcqk_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})  order by c.ts_xgsj desc
    </select>
</mapper>
