<?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.zwnw.rlzysc.entity.VRsRlzyGwtjMapper">
    <insert id="saveZpxxTjxx" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="vrsrlzygwtjpo">
            INSERT INTO v_rs_rlzy_gwtj
            (rs_rlzy_gwtj_lsh, rs_yhlsh, rs_rlzy_gwxx_lsh, tjyj, bae001, bae002, bae003, bae004, bae005, bae006)
            VALUES
            (
            #{vrsrlzygwtjpo.rsrlzygwtjlsh},
            #{vrsrlzygwtjpo.rsyhlsh},
            #{vrsrlzygwtjpo.rsrlzygwxxlsh},
            #{vrsrlzygwtjpo.tjyj},
            #{vrsrlzygwtjpo.bae001},
            #{vrsrlzygwtjpo.bae002},
            #{vrsrlzygwtjpo.bae003},
            #{vrsrlzygwtjpo.bae004},
            #{vrsrlzygwtjpo.bae005},
            #{vrsrlzygwtjpo.bae006}
            );
        </foreach>
        end;
    </insert>

    <select id="getTjr" parameterType="com.zjrs.zwnw.rlzysc.dto.TjrQueryDTO" resultType="HashMap">
        select v.aac003 as "xm", v.aac058 as "zjlx",v.aac147 as "zjhm",v.useridcode as "rsyhlsh", v.qwzw as "qwzw"
        from (
        select c.aac003,
        c.aac058,
        c.aac147,
        c.aac067,
        b.userid useridcode,
        (select decode(count(d.rs_rlzy_hmdgl_lsh), 0, '1', '2')
        from v_rs_rlzy_hmdgl d
        where d.rs_rlzy_ts_lsh = b.userid and d.ts_yxbz = '1') zt,
        (select decode(count(e.rs_rlzy_jlxx_lsh), 0, '0', '1') from v_rs_rlzy_jlxx e
        where e.ts_yxbz = '1' and e.rs_yhlsh = b.userid) ishavejl,
        ( SELECT listagg((SELECT aaa103 FROM V_GGFW_DICT WHERE AAA100 = 'RS_RLZY_ZYXX' AND aaa102 = qwzw), ',')
        WITHIN GROUP(ORDER BY rs_rlzy_qzyx_LSH)   FROM rs_rlzy_qzyx WHERE rs_yhlsh = b.USERID) qwzw
        from rs_ggfw_user b, v_s_ac01 c
        where b.ztid = c.ztid ) v
        where v.zt='1'
        <if test="dto.zjlx != null and dto.zjlx != ''">
            and v.aac058 = #{dto.zjlx}
        </if>
        <if test="dto.zjhm != null and dto.zjhm != ''">
            and v.aac147 = #{dto.zjhm}
        </if>
    </select>

    <select id="grqztjList" parameterType="com.zjrs.zwnw.rlzysc.dto.CxtjQueryDTO" resultType="HashMap">
        SELECT MC "mc", ZJHM "zjhm", SJHM "sjhm",
        (SELECT listagg((SELECT aaa103 FROM V_GGFW_DICT WHERE AAA100 = 'RS_RLZY_ZYXX' AND aaa102 = qwzw), ',')
        WITHIN GROUP(ORDER BY rs_rlzy_qzyx_LSH)   FROM rs_rlzy_qzyx WHERE rs_yhlsh = b.USERID) "qzzw",
        (SELECT GSMC FROM RS_RLZY_QYXX T1, RS_RLZY_ZWXX T2 WHERE T1.RS_RLZY_QYXX_LSH  = T2.RS_RLZY_QYXX_LSH AND T2.RS_RLZY_ZWXX_LSH  = A.RS_RLZY_GWXX_LSH) "gsmc",
        TJYJ "tjyj",
        (SELECT USER_NAME FROM S_SG_USER WHERE ID  = BAE002) "bae002", BAE003 "bae003"  FROM RS_RLZY_GWTJ a, RS_GGFW_USER b
        WHERE a.RS_YHLSH = b.USERID
        <if test="dto.startDateTime != null and dto.startDateTime != ''">
            and BAE003 <![CDATA[ >= ]]> #{dto.startDateTime}||' 00:00:00'
        </if>
        order by bae003 desc
    </select>

    <select id="exprotGrqztjList" parameterType="com.zjrs.zwnw.rlzysc.dto.CxtjQueryDTO" resultType="com.zjrs.zwnw.rlzysc.dto.CxtjGrqztjcxDTO">
        SELECT MC "mc", ZJHM "zjhm", SJHM "sjhm",
        (SELECT listagg((SELECT aaa103 FROM V_GGFW_DICT WHERE AAA100 = 'RS_RLZY_ZYXX' AND aaa102 = qwzw), ',')
        WITHIN GROUP(ORDER BY rs_rlzy_qzyx_LSH)   FROM rs_rlzy_qzyx WHERE rs_yhlsh = b.USERID) "qzzw",
        (SELECT GSMC FROM RS_RLZY_QYXX T1, RS_RLZY_ZWXX T2 WHERE T1.RS_RLZY_QYXX_LSH  = T2.RS_RLZY_QYXX_LSH AND T2.RS_RLZY_ZWXX_LSH  = A.RS_RLZY_GWXX_LSH) "gsmc",
        TJYJ "tjyj",
        (SELECT USER_NAME FROM S_SG_USER WHERE ID  = BAE002) "bae002", BAE003 "bae003"  FROM RS_RLZY_GWTJ a, RS_GGFW_USER b
        WHERE a.RS_YHLSH = b.USERID
        <if test="dto.startDateTime != null and dto.startDateTime != ''">
            and BAE003 <![CDATA[ >= ]]> #{dto.startDateTime}||' 00:00:00'
        </if>
        order by bae003 desc
    </select>

</mapper>
