<?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.ZzzdMapper">

    <select id="getZphlist" parameterType="com.zjrs.ggfw.rlzysc.dto.zzzd.ZzzdZphListQueryDTO" resultType="java.util.Map">
        select
        v.zphmc as "zphmc",
        v.jbcs  as "jbcs",
        v.cs as "cs",
        v.jbrqc as "jbrqc",
        v.kssj as "kssj",
        v.jbrqz as "jbrqz",
        v.jssj as "jssj",
        v.zphlx as "zphlx",
        v.rs_rlzy_zphgl_lsh as "rsrlzyzphgllsh"
        from (select g.*,
        (case
        when (g.zphzt = '0' and
        to_date(g.jbrqc || ' ' || g.kssj,
        'YYYY-MM-DD HH24:MI:SS') > sysdate) then
        '1'
        when (g.zphzt = '0' and
        ((to_date(g.jbrqz || ' ' || g.jssj,
        'YYYY-MM-DD HH24:MI:SS') >
        sysdate) and
        (sysdate >
        to_date(g.jbrqc || ' ' || g.kssj,
        'YYYY-MM-DD HH24:MI:SS')))) then
        '2'
        when (g.zphzt = '1' or
        sysdate >
        to_date(g.jbrqz || ' ' || g.jssj,
        'YYYY-MM-DD HH24:MI:SS')) then
        '3'
        else
        ''
        end) uzphzt
        from v_rs_rlzy_zphgl g
        where g.ts_yxbz = '1') v
        where 1 = 1 
        <if test="dto.cs != null and dto.cs != ''">
            and v.cs = #{dto.cs}
        </if>
        <if test="dto.zphlx != null and dto.zphlx != ''">
            and v.zphlx = #{dto.zphlx}
        </if>
    </select>

    <select id="getZwListToQy" parameterType="com.zjrs.ggfw.rlzysc.dto.zzzd.ZzzdZwListQueryForDwDTO" resultType="java.util.Map">
        select
        p.zwmc as "zwmc",
        p.zprs as "zprs",
        p.xz as "xz",
        p.gzjy as "gzjy",
        p.gzdd as "gzdd",
        p.rs_rlzy_zwxx_lsh as "rsrlzyzwxxlsh"
        from (select distinct a.rs_rlzy_zwgl_lsh
        from v_rs_rlzy_zwwzyysh a
        where a.rs_rlzy_zphgl_lsh = #{dto.rsrlzyzphgllsh}
        and a.rs_rlzy_qyxx_lsh = #{dto.rsrlzyqyxxlsh}
        and a.shjg = '1') v,
        v_rs_rlzy_zwwzyyzwxx q,
        v_rs_rlzy_zwxx p
        where v.rs_rlzy_zwgl_lsh = q.rs_rlzy_zwgl_lsh
        and p.rs_rlzy_zwxx_lsh = q.rs_rlzy_zwxx_lsh
    </select>

    <select id="getZwList" parameterType="com.zjrs.ggfw.rlzysc.dto.zzzd.ZzzdZwListQueryDTO" resultType="java.util.Map">
        select
        v.zwmc as "zwmc",
        v.whcd as "whcd",
        v.xz as "xz",
        v.gzxz as "gzxz",
        v.aab004 as "aab004",
        v.gzjy as "gzjy",
        v.rs_rlzy_zwxx_lsh as "rsrlzyzwxxlsh"
        from (select p.*,
        decode((select count(rs_rlzy_hmdgl_lsh)
        from v_rs_rlzy_hmdgl c
        where q.rs_yhlsh = c.rs_rlzy_ts_lsh
        and c.ts_yxbz = '1'),
        0,
        '0',
        '1') ishmd,q.rs_rlzy_qyxx_lsh, q.zpdq, g.aab004
        from v_rs_rlzy_zwxx p, v_rs_rlzy_qyxx q,v_tyrz_user_dwjbxx g
        where 1 = 1
        and p.rs_rlzy_qyxx_lsh = q.rs_rlzy_qyxx_lsh
        and q.rs_yhlsh = g.useridcode
        and q.qyxx_shzt = '1') v
        where v.ishmd = '0'
        <if test="dto.zpdq != null and dto.zpdq != ''">
            and v.zpdq = #{dto.zpdq}
        </if>
        <if test="dto.xz != null and dto.xz != ''">
            and v.xz = #{dto.xz}
        </if>
    </select>

    <select id="getQyList" parameterType="com.zjrs.ggfw.rlzysc.dto.grzxjlxx.ZjQueryListDTO" resultType="java.util.Map">
        select c.aab004 as "gsmc",
        a.rs_rlzy_zphgl_lsh,
        a.rs_rlzy_hcgl_lsh,
        a.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",
        c.aae004 as "lxr",
        c.aae006 as "lxdz",
        b.czhm as "czhm",
        d.hcmc as "hcmc",
        (select to_char(count(rs_zwwz_lsh))
        from v_rs_rlzy_zwwzyysh t1
        where t1.rs_rlzy_qyxx_lsh = a.rs_rlzy_qyxx_lsh and t1.rs_rlzy_hcgl_lsh = a.rs_rlzy_hcgl_lsh) as "zws"
        from v_rs_rlzy_zwwzyysh a, v_rs_rlzy_qyxx b, v_tyrz_user_dwjbxx c, v_rs_rlzy_hcgl d
        where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and a.rs_rlzy_hcgl_lsh = d.rs_rlzy_hcgl_lsh
        and a.rs_rlzy_zphgl_lsh = #{dto.zjlsh}
        group by
        a.rs_rlzy_zphgl_lsh,
        a.rs_rlzy_hcgl_lsh,
        c.aab004,
        a.rs_rlzy_qyxx_lsh,
        c.aae004, c.aae006, b.czhm, d.hcmc
    </select>
</mapper>
