<?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.VRsRlzyQztjMapper">
    <insert id="saveQzxxTjxx" useGeneratedKeys="false">
        begin
        <foreach collection="list" index="index" item="vrsrlzyqztjpo">
            INSERT INTO v_rs_rlzy_qztj
            (rs_rlzy_qztj_lsh, rs_rlzy_jlxx_lsh, rs_rlzy_qyxx_lsh, tjyj, bae001, bae002, bae003, bae004, bae005, bae006)
            VALUES
            (
            #{vrsrlzyqztjpo.rsrlzyqztjlsh},
            #{vrsrlzyqztjpo.rsrlzyjlxxlsh},
            #{vrsrlzyqztjpo.rsrlzyqyxxlsh},
            #{vrsrlzyqztjpo.tjyj},
            #{vrsrlzyqztjpo.bae001},
            #{vrsrlzyqztjpo.bae002},
            #{vrsrlzyqztjpo.bae003},
            #{vrsrlzyqztjpo.bae004},
            #{vrsrlzyqztjpo.bae005},
            #{vrsrlzyqztjpo.bae006}
            );
        </foreach>
        end;
    </insert>

    <select id="getTjdw" parameterType="com.zjrs.zwnw.rlzysc.dto.TjdwQueryDTO" resultType="HashMap">
        select v.aab004 as "gsmc", v.aab998 as "tyshxydm", f.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",
        (SELECT listagg(t2.zwmc, ',') WITHIN GROUP(ORDER BY t2.RS_RLZY_QYXX_LSH)  FROM rs_rlzy_qyxx t1, rs_rlzy_zwxx t2 WHERE t1.rs_rlzy_qyxx_lsh = t2.rs_rlzy_qyxx_lsh
        AND t1.RS_RLZY_QYXX_LSH  = f.RS_RLZY_QYXX_LSH AND t2.TS_YXBZ = '1' AND t2.FBZT = '1') "zwmc"
        from (select *
        from (select c.aab004,
        c.aab013,
        c.aae005,
        c.aab998,
        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
        from rs_ggfw_user b, v_s_ab01 c
        where b.fwdx = '2'
        and b.ztid = c.ztid)
        where zt = '1') v,
        v_rs_rlzy_qyxx f
        where v.useridcode = f.rs_yhlsh and v.zt='1'
<!--        and f.qyxx_shzt = '1'-->
        <if test="dto.tyshxydm != null and dto.tyshxydm != ''">
            and f.AAB998 = #{dto.tyshxydm}
        </if>
        <if test="dto.gsmc != null and dto.gsmc != ''">
            and f.gsmc like '%'||#{ dto.gsmc }||'%'
        </if>
    </select>

    <select id="dwtjcxList" parameterType="com.zjrs.zwnw.rlzysc.dto.CxtjQueryDTO" resultType="HashMap">
        SELECT B.GSMC "gsmc",A.TJYJ "tjyj", f_jbxxgl_getAllAreaNameLine(b.aab078) as "xzqh",
        c.AAC147 "aac147", c.aac003 "aac003", c.sjhm "sjhm",
        (SELECT listagg(t2.zwmc, ',') WITHIN GROUP(ORDER BY t2.RS_RLZY_QYXX_LSH)  FROM rs_rlzy_qyxx t1, rs_rlzy_zwxx t2 WHERE t1.rs_rlzy_qyxx_lsh = t2.rs_rlzy_qyxx_lsh
        AND t1.RS_RLZY_QYXX_LSH  = b.RS_RLZY_QYXX_LSH AND t2.TS_YXBZ = '1' AND t2.FBZT = '1') "zwmc",
        (SELECT USER_NAME FROM S_SG_USER WHERE ID  = BAE002) "bae002", BAE003 "bae003"
        FROM v_rs_rlzy_qztj A, RS_RLZY_QYXX B, RS_RLZY_JLXX C WHERE A.RS_RLZY_QYXX_LSH = B.RS_RLZY_QYXX_LSH
        AND A.RS_RLZY_JLXX_LSH  = C.RS_RLZY_JLXX_LSH
        <if test="dto.startDateTime != null and dto.startDateTime != ''">
            and BAE003 <![CDATA[ >= ]]> #{dto.startDateTime}||' 00:00:00'
        </if>
        order by bae003 desc
    </select>

    <select id="exprotDwtjcxList" parameterType="com.zjrs.zwnw.rlzysc.dto.CxtjQueryDTO" resultType="com.zjrs.zwnw.rlzysc.dto.CxtjDwtjcxDTO">
        SELECT B.GSMC "gsmc",A.TJYJ "tjyj", f_jbxxgl_getAllAreaNameLine(b.aab078) as "xzqh",
        c.AAC147 "aac147", c.aac003 "aac003", c.sjhm "sjhm",
        (SELECT listagg(t2.zwmc, ',') WITHIN GROUP(ORDER BY t2.RS_RLZY_QYXX_LSH)  FROM rs_rlzy_qyxx t1, rs_rlzy_zwxx t2 WHERE t1.rs_rlzy_qyxx_lsh = t2.rs_rlzy_qyxx_lsh
        AND t1.RS_RLZY_QYXX_LSH  = b.RS_RLZY_QYXX_LSH AND t2.TS_YXBZ = '1' AND t2.FBZT = '1') "zwmc",
        (SELECT USER_NAME FROM S_SG_USER WHERE ID  = BAE002) "bae002", BAE003 "bae003"
        FROM v_rs_rlzy_qztj A, RS_RLZY_QYXX B, RS_RLZY_JLXX C WHERE A.RS_RLZY_QYXX_LSH = B.RS_RLZY_QYXX_LSH
        AND A.RS_RLZY_JLXX_LSH  = C.RS_RLZY_JLXX_LSH
        <if test="dto.startDateTime != null and dto.startDateTime != ''">
            and BAE003 <![CDATA[ >= ]]> #{dto.startDateTime}||' 00:00:00'
        </if>
        order by bae003 desc
    </select>

    <select id="jlyllsList" parameterType="com.zjrs.zwnw.rlzysc.dto.CxtjQueryDTO" resultType="HashMap">
        SELECT B.GSMC "gsmc", f_jbxxgl_getAllAreaNameLine(B.aab078) as "xzqh", C.aac003 AS "xm", c.AAC147 AS "sfzhm", a.ts_ot AS "ylsj"
        FROM RS_RLZY_FWRZ A, RS_RLZY_QYXX B, RS_RLZY_JLXX C
        WHERE A.RS_RLZY_SOURCE_LSH = B.RS_RLZY_QYXX_LSH AND A.RS_RLZY_TARGET_LSH = C.RS_RLZY_JLXX_LSH
        AND TYPE = '01'
        <if test="dto.gsmc != null and dto.gsmc != ''">
            AND b.gsmc like '%'||#{dto.gsmc}||'%'
        </if>
        order by a.ts_ot desc
    </select>

    <select id="exprotjlyllsList" parameterType="com.zjrs.zwnw.rlzysc.dto.CxtjQueryDTO" resultType="com.zjrs.zwnw.rlzysc.dto.CxtjJlyllscxDTO">
        SELECT B.GSMC "gsmc", f_jbxxgl_getAllAreaNameLine(B.aab078) as "xzqh", C.aac003 AS "xm", c.AAC147 AS "sfzhm", a.ts_ot AS "ylsj"
        FROM RS_RLZY_FWRZ A, RS_RLZY_QYXX B, RS_RLZY_JLXX C
        WHERE A.RS_RLZY_SOURCE_LSH = B.RS_RLZY_QYXX_LSH AND A.RS_RLZY_TARGET_LSH = C.RS_RLZY_JLXX_LSH
        AND TYPE = '9'
        <if test="dto.gsmc != null and dto.gsmc != ''">
            AND b.gsmc like '%'||#{dto.gsmc}||'%'
        </if>
        order by a.ts_ot desc
    </select>

</mapper>
