<?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.ZphglMapper">
    <select id="getZphDetail" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyXczphPO">
        select a.rs_rlzy_zphgl_lsh rsrlzyzphgllsh,
        a.zt,
        a.jbrqc,
        a.jbrqz,
        a.kssj,
        a.jssj,
        a.jbcs,
        a.zphlx,
        a.zphmc,
        a.bmzt,
        a.jbdw,
        a.lxzj,
        a.cs,
        a.cbdw,
        '' zphztu,
        a.xbdw from v_rs_rlzy_zphgl a where a.rs_rlzy_zphgl_lsh = #{rsrlzyzphgllsh}
    </select>

    <select id="getZphglList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyXczphPO">
        select * from
        (select a.rs_rlzy_zphgl_lsh rsrlzyzphgllsh,
        a.zt,
        a.zphjblx,
        a.jbrqc,
        a.jbrqz,
        a.kssj,
        a.jssj,
        a.jbcs,
        a.zphlx,
        a.zphmc,
        a.bmzt,
        '' zphztu,
        a.zdzt from  v_rs_rlzy_zphgl a where         
        <if test="zphjblx != null and zphjblx != ''">
             a.zphjblx = #{zphjblx}
        </if>
        order by a.ZDSJ desc nulls last,a.jbrqc)d where d.zphztu !='3'
    </select>


    <select id="getZxscZphxx" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyXczphPO">
          select zjzph.RS_RLZY_ZPHGL_LSH rsrlzyzphgllsh,
        zjzph.ZPHMC,
        zjzph.JBRQC,
        zjzph.KSSJ,
        zjzph.JSSJ,
        zjzph.JBRQZ,
        zjzph.JBDW,
        zjzph.JBCS,
        zjzph.CBDW,
        zjzph.XBDW,
        (select count(1) from v_rs_rlzy_zwwzyyzwxx where rs_rlzy_zphgl_lsh = zjzph.rs_rlzy_zphgl_lsh) cnt
   from (select a.*,
                '' zphztu
           from v_RS_RLZY_ZPHGL a
          where 
        <if test="dto.jbrqc != null and dto.jbrqc != ''">
             a.jbrqc = #{dto.jbrqc}
        </if>
          order by a.bae003 desc) zjzph
  where zjzph.zphztu != '3'
    </select>

    <select id="getQtzphList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyXczphPO">
          select * from (
        select a.rs_rlzy_zphgl_lsh rsrlzyzphgllsh,
        a.zt,
        a.jbrqc,a.jbrqz,a.kssj,a.jssj,a.jbcs,
        '' zphztu, zphmc from  v_rs_rlzy_zphgl a where a.ts_yxbz = '1' AND a.fbzt = '1'
        and a.rs_rlzy_zphgl_lsh not in (select rs_rlzy_zphgl_lsh from v_rs_rlzy_zphgl where
        rs_rlzy_zphgl_lsh = #{dto.rsrlzyzphgllsh}
        )
        order by a.bae003 desc)d 
    </select>

    <select id="getZphCalendarList" resultType="java.util.Map">
        select jbrqc, count(jbrqc) cnt
   from (select a.*,
                '' zphztu
           from v_RS_RLZY_ZPHGL a
          where a.ts_yxbz = '1'
            and a.fbzt = '1'
          order by a.bae003 desc) zjzph
          group by jbrqc
    </select>

    <select id="getgetQyzW" resultType="java.util.Map">
        select count(a.rs_rlzy_zwxx_lsh) as "zwcnt"
        from v_rs_rlzy_zwxx a,v_rs_rlzy_zwwzyyzwxx b,v_rs_rlzy_qyxx c
        where b.rs_rlzy_zphgl_lsh = #{rsrlzyzphgllsh}
        and a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh and a.ZPFS in ('02','05')
        and a.rs_rlzy_zwxx_lsh = b.rs_rlzy_zwxx_lsh
        and c.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh 
    </select>

    <select id="getQyList" resultType="java.util.Map">
        select a.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",
        b.gsgm as "gsgm",
        b.zczb as "zczb",
        (select aaa103
        from v_ggfw_dict
        where aaa100 = 'JBXX_AAB022'
        and aaa102 = d.aab022) as "aab022",
        d.aab004 as "gsmc",
        d.aab004 as "aab004" from (select distinct rs_rlzy_qyxx_lsh,ts_cjsj
        from rs_rlzy_zwwzyyzwxx
        where rs_rlzy_zphgl_lsh =
        #{rsrlzyzphgllsh}) a,
        v_rs_rlzy_qyxx b,
        rs_ggfw_user c,
        v_ab01 d where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and c.userid = b.rs_yhlsh and c.ztid = d.ztid
        order by a.ts_cjsj
    </select>

    <select id="getZxQyList" resultType="java.util.Map">
        select a.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",
        c.gsgm as "gsgm",
        c.zczb as "zczb",
        (select aaa103
        from v_ggfw_dict
        where aaa100 = 'JBXX_AAB022'
        and aaa102 = e.aab022) as "aab022",
        e.aab004 as "aab004",
        a.ts_cjsj as "jrsj"
        from rs_rlzy_zwwzyyzwxx a,
        RS_RLZY_ZPHGL      b,
        v_rs_rlzy_qyxx     c,
        rs_ggfw_user       d,
        v_ab01             e
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and b.zphjblx = '02'
        and a.rs_rlzy_qyxx_lsh = c.rs_rlzy_qyxx_lsh
        and d.userid = c.rs_yhlsh
        and d.ztid = e.ztid
        order by a.ts_cjsj desc

    </select>

    <select id="getChdwDetail" parameterType="map" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZwxxPO">
        select a.zwmc as "zwmc"
        from v_rs_rlzy_zwxx a,v_rs_rlzy_zwwzyyzwxx b,v_rs_rlzy_qyxx c
        where b.rs_rlzy_zphgl_lsh = #{dto.rsrlzyzphgllsh}
        and a.rs_rlzy_qyxx_lsh = #{dto.rsrlzyqyxxlsh}
        and a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh and a.ZPFS in ('0','2')
        and a.rs_rlzy_zwxx_lsh = b.rs_rlzy_zwxx_lsh
        and c.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh 
    </select>

    <select id="getKydxw" parameterType="com.zjrs.ggfw.rlzysc.dto.QtzphQueryDTO" resultType="map">
        select count(1)||'' "kyd",a.rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh"
        from v_rs_rlzy_zphgl a,v_rs_rlzy_hcgl b,v_rs_rlzy_zwgl c,v_rs_rlzy_zwwzgl d
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and b.rs_rlzy_hcgl_lsh = c.rs_rlzy_hcgl_lsh
        AND c.RS_RLZY_ZWGL_LSH = d.RS_RLZY_ZWGL_LSH
        and a.rs_rlzy_zphgl_lsh = #{dto.rsrlzyzphgllsh}
        GROUP BY a.rs_rlzy_zphgl_lsh
    </select>
</mapper>
