<?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.ScgzMapper">
    
    <select id="getDwzpScJlxxList" parameterType="com.zjrs.ggfw.rlzysc.dto.dwzp.DwzpJlListQueryDTO" resultType="HashMap">
        select a.rs_rlzy_scgz_lsh "rsRlzyScgzLsh",a.rs_rlzy_jlxx_lsh "rsRlzyJlxxLsh",
            a.rs_rlzy_qyxx_lsh "rsRlzyQyxxLsh",a.sczt "sczt",
            b.xm "xm",b.cjgzsj "cjgzsj",d.qwzw "qwzw",b.ts_xl "xl",d.qwxz "qwxz",b.ts_qzlx "qzlx",
            b.tx_tycc_lsh "txTyccid",b.sp_tycc_lsh "spTyccid",'01' "msyqzt",
            b.shjg "shjg",b.txshjg "txshjg",'01' "spshjg"
        from v_rs_rlzy_scgz a,v_rs_rlzy_jlxx b,v_rs_rlzy_qzyx_dyb c,v_rs_rlzy_qzyx d
        where a.rs_rlzy_jlxx_lsh=b.rs_rlzy_jlxx_lsh and b.rs_rlzy_jlxx_lsh=c.rs_rlzy_jlxx_lsh 
            and c.rs_rlzy_qzyx_lsh=d.rs_rlzy_qzyx_lsh and a.rs_rlzy_qyxx_lsh = #{dto.rsRlzyQyxxLsh}
        <if test="dto.sczt != null and dto.sczt != ''">
            and a.sczt = #{dto.sczt}
        </if>
        <if test="dto.cjgzsjKsrq != null and dto.cjgzsjKsrq != ''">
            and b.cjgzsj >= #{dto.cjgzsjKsrq}
        </if>
        <if test="dto.cjgzsjJsrq != null and dto.cjgzsjJsrq != ''">
            and b.cjgzsj &lt;= #{dto.cjgzsjJsrq}
        </if>
        order by a.ts_xgsj DESC   
    </select>
    
    <select id="getDwzpGzJlxxList" parameterType="com.zjrs.ggfw.rlzysc.dto.dwzp.DwzpJlListQueryDTO" resultType="HashMap">
        select a.rs_rlzy_gzgs_lsh "rsRlzyGzgsLsh",b.rs_rlzy_jlxx_lsh "rsRlzyJlxxLsh",
            a.rs_rlzy_qyxx_lsh "rsRlzyQyxxLsh",
            b.xm "xm",b.cjgzsj "cjgzsj",d.qwzw "qwzw",b.ts_xl "xl",d.qwxz "qwxz",b.ts_qzlx "qzlx",
            b.tx_tycc_lsh "txTyccid",b.sp_tycc_lsh "spTyccid",(select count(1) from v_rs_rlzy_scgz 
            where rs_rlzy_qyxx_lsh=a.rs_rlzy_qyxx_lsh and rs_rlzy_jlxx_lsh=b.rs_rlzy_jlxx_lsh and ts_yxbz='1' and sczt='1') "sczt",
            b.shjg "shjg",b.txshjg "txshjg",'01' "spshjg"
        from v_rs_rlzy_gzgs a,v_rs_rlzy_jlxx b,v_rs_rlzy_qzyx_dyb c,v_rs_rlzy_qzyx d
        where a.rs_yhlsh=b.rs_yhlsh and b.rs_rlzy_jlxx_lsh=c.rs_rlzy_jlxx_lsh 
            and c.rs_rlzy_qzyx_lsh=d.rs_rlzy_qzyx_lsh 
            and a.rs_rlzy_qyxx_lsh = #{dto.rsRlzyQyxxLsh} 
        <if test="dto.xm != null and dto.xm != ''">
            and b.xm like '%${ dto.xm }%'
        </if>
        <if test="dto.zwmc != null and dto.zwmc != ''">
            and d.qwzw like '%${dto.zwmc}%'
        </if>
        <if test="dto.qwxzq != null and dto.qwxzq != ''">
            and d.qwxz >= #{dto.qwxzq}
        </if>
        <if test="dto.qwxzz != null and dto.qwxzz != ''">
            and d.qwxz &lt;= #{dto.qwxzz}
        </if>
        order by a.ts_cjsj DESC   
    </select>
    
</mapper>
