<?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.VRsRlzyMsglMapper">
    <select id="getMsgllist" parameterType="Map" resultType="HashMap">
        select v.xm        as "xm",
        v.lxfs             as "lxfs",
        v.qwzw             as "qwzw",
        v.msrq             as "msrq",
        v.kssj             as "kssj",
        v.jssj             as "jssj",
        v.mszt             as "mszt",
        v.msjg             as "msjg",
        v.rs_yhlsh         as "rsyhlsh",
        v.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",
        v.rs_rlzy_gwxx_lsh as "rsrlzyzwxxlsh",
        (case when to_date(v.msrq||' '||v.jssj,'YYYY-MM-DD HH24:MI:SS') > sysdate then '0'
        else '1' end ) as "gqzt",
        v.rs_rlzy_msgl_lsh as "rsrlzymsgllsh",
        v.mssm             as "mssm"
        from (select (select d.aac003
        from v_tyrz_user_grjbxx d
        where d.useridcode = w.rs_yhlsh) xm,
        (select d.aac067
        from v_tyrz_user_grjbxx d
        where d.useridcode = w.rs_yhlsh) lxfs,
        (select e.zwmc
        from v_rs_rlzy_zwxx e
        where e.rs_rlzy_zwxx_lsh = w.rs_rlzy_gwxx_lsh) qwzw,
        w.*
        from (select p.*,
        (select c.rs_yhlsh
        from v_rs_rlzy_jlxx c
        where p.rs_rlzy_ts_lsh = c.rs_rlzy_jlxx_lsh) rs_yhlsh
        from (select a.msrq,
        a.kssj,
        a.jssj,
        a.mszt,
        a.msjg,
        a.rs_rlzy_msgl_lsh,
        a.rs_rlzy_qyxx_lsh,
        a.mssm,
        a.rs_rlzy_gwxx_lsh,
        (select b.rs_rlzy_ts_lsh
        from v_rs_rlzy_msrygl b
        where a.rs_rlzy_msgl_lsh =
        b.rs_rlzy_msgl_lsh
        and b.msrylx = '2') rs_rlzy_ts_lsh
        from v_rs_rlzy_msgl a
        where a.rs_rlzy_qyxx_lsh = #{map.rsrlzyqyxxlsh}) p) w) v
        where 1 = 1
        <if test="map.zwmc != null and map.zwmc != ''">
            and v.qwzw =  #{map.zwmc}
        </if>
    </select>


    <select id="getMsgllistForGr" parameterType="Map" resultType="HashMap">
        select v.msdd as "msdd",
        v.msrq as "msrq",
        v.mszt as "mszt",
        v.rs_rlzy_msgl_lsh as "rsrlzymsgllsh",
        v.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",
        v.rs_rlzy_gwxx_lsh as "rsrlzygwxxlsh",
        v.qymc as "qymc",
        v.qwxz as "qwxz",
        v.qwzw as "qwzw",
        v.sfksms as "sfksms"
        from (select b.msrq,
        b.msdd,
        b.mszt,
        b.sfksms,
        b.rs_rlzy_msgl_lsh,
        b.rs_rlzy_qyxx_lsh,
        b.rs_rlzy_gwxx_lsh,
        (select d.aab004
        from v_tyrz_user_dwjbxx d, v_rs_rlzy_qyxx c
        where d.useridcode = c.rs_yhlsh
        and c.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh) qymc,
        (select e.qwxz
        from v_rs_rlzy_qzyx e, v_rs_rlzy_qzyx_dyb f
        where e.rs_rlzy_qzyx_lsh = f.rs_rlzy_qzyx_lsh
        and f.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh) qwxz,
        (select e.zwmc
        from v_rs_rlzy_zwxx e
        where e.rs_rlzy_zwxx_lsh = b.rs_rlzy_gwxx_lsh) qwzw
        from v_rs_rlzy_jlxx a, v_rs_rlzy_msgl b
        where a.rs_yhlsh = #{map.userid}
        and a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh) v where 1=1
        <if test="map.msrq != null and map.msrq != ''">
            and v.msrq = #{map.msrq}
        </if>
    </select>

    <select id="getMsglInfo" parameterType="Map" resultType="HashMap">
        select v.xm               as "xm",
        v.lxfs             as "lxfs",
        v.qwzw             as "qwzw",
        v.msrq             as "msrq",
        v.kssj             as "kssj",
        v.jssj             as "jssj",
        v.mszt             as "mszt",
        v.rs_rlzy_msgl_lsh as "rsrlzymsgllsh",
        v.sfksms           as "sfksms",
        v.mssm             as "mssm",
        v.rs_rlzy_qyxx_lsh as "rs_rlzy_qyxx_lsh",
        v.rs_rlzy_gwxx_lsh as "rs_rlzy_gwxx_lsh",
        v.rs_rlzy_jlxx_lsh as "rs_rlzy_jlxx_lsh"
        from (select (select d.aac003
        from v_tyrz_user_grjbxx d
        where d.useridcode = w.rs_yhlsh) xm,
        (select d.aac067
        from v_tyrz_user_grjbxx d
        where d.useridcode = w.rs_yhlsh) lxfs,
        (select e.zwmc
        from v_rs_rlzy_zwxx e
        where e.rs_rlzy_zwxx_lsh = w.rs_rlzy_gwxx_lsh) qwzw,
        w.*
        from (select p.*,
        (select c.rs_yhlsh
        from v_rs_rlzy_jlxx c
        where p.rs_rlzy_ts_lsh = c.rs_rlzy_jlxx_lsh) rs_yhlsh
        from (select a.msrq,
        a.kssj,
        a.jssj,
        a.mszt,
        a.rs_rlzy_msgl_lsh,
        a.mssm,
        a.rs_rlzy_gwxx_lsh,
        a.sfksms,
        (select b.rs_rlzy_ts_lsh
        from v_rs_rlzy_msrygl b
        where a.rs_rlzy_msgl_lsh =
        b.rs_rlzy_msgl_lsh
        and b.msrylx = '2') rs_rlzy_ts_lsh,
        a.rs_rlzy_qyxx_lsh,
        a.rs_rlzy_jlxx_lsh
        from v_rs_rlzy_msgl a
        where a.rs_rlzy_msgl_lsh = #{map.zjlsh}) p) w) v
        where 1 = 1
    </select>

    <select id="getMsglInfoForYdd" parameterType="Map" resultType="HashMap">
        select b.gzxz as "gzxz",
        a.msrq as "msrq",
        a.msdd as "msdd",
        a.sfksms as "sfksms",
        a.mszt as "mszt",
        b.zprs as "zprs",
        b.whcd as "whcd"
        from v_rs_rlzy_msgl a,v_rs_rlzy_zwxx b
        where a.rs_rlzy_gwxx_lsh = b.rs_rlzy_zwxx_lsh
        and rs_rlzy_msgl_lsh = #{map.zjlsh}
    </select>

    <select id="getMsglMsglist" resultType="java.util.Map">
        select b.xm as "xm", a.rs_rlzy_msgl_lsh as "rs_rlzy_msgl_lsh",
        b.rs_rlzy_msggl_lsh  as "rs_rlzy_msggl_lsh",
        c.rs_rlzy_jlxx_lsh as "rs_rlzy_jlxx_lsh",
        c.rs_rlzy_gwxx_lsh as "rs_rlzy_gwxx_lsh",
        c.rs_rlzy_qyxx_lsh as "rs_rlzy_qyxx_lsh",
        c.jwt_token as "jwt_token"
        from v_rs_rlzy_msrygl a,v_rs_rlzy_msggl b, v_rs_rlzy_msgl c
        where a.rs_rlzy_ts_lsh = b.rs_rlzy_msggl_lsh
        and a.rs_rlzy_msgl_lsh = c.rs_rlzy_msgl_lsh
        and a.rs_rlzy_msgl_lsh = #{map.id}
    </select>

    <select id="getMszInfo" resultType="java.util.Map">
        select d.aac003 as "aac003",d.aac004 as "aac004"
        from v_tyrz_user_grjbxx d
        where d.useridcode = #{rsyhlsh}
    </select>
    <select id="getMsqyInfo" resultType="java.util.Map">
        select a.aab004 as "aab004"
        from v_tyrz_user_dwjbxx a,v_rs_rlzy_qyxx b
        where a.useridcode = b.rs_yhlsh
        and b.rs_rlzy_qyxx_lsh = #{rsrlzyqyxxlsh}
    </select>


</mapper>
