<?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.ZwxxMapper">
    <select id="getZwtjListByLatest" resultType="com.zjrs.ggfw.rlzysc.dto.ZwxxListDTO">
        select z.*,ROWNUM from (select b.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,c.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,
        b.zwmc zwmc,c.zpdq zpdq, b.ts_xgsj fbsj,b.xz xz,b.gzjy gzjy,
        b.whcd xl,b.fldy gsfl,c.gsmc gsmc from rs_rlzy_zwxx b,rs_rlzy_qyxx c where b.rs_rlzy_qyxx_lsh =
        c.rs_rlzy_qyxx_lsh
        order by b.ts_xgsj desc)z
        where ROWNUM &lt; 7
    </select>

    <select id="getZwtjListByaSlary" resultType="com.zjrs.ggfw.rlzysc.dto.ZwxxListDTO">
        select z.*,ROWNUM from (select b.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,c.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,
        b.zwmc zwmc,c.zpdq zpdq,b.ts_xgsj fbsj,b.xz xz,b.gzjy gzjy,
        b.whcd xl,b.fldy gsfl,c.gsmc gsmc from rs_rlzy_zwxx b,rs_rlzy_qyxx c where b.rs_rlzy_qyxx_lsh =
        c.rs_rlzy_qyxx_lsh
        order by b.xz desc)z
        where ROWNUM &lt; 7
    </select>

    <select id="getMoreZwtj" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZwxxPO">
        select zwxxb.*,ROWNUM from (select
        zwxx.RS_RLZY_ZWXX_LSH,zwxx.ZWMC,zwxx.DWDZ,zwxx.YX,zwxx.WHCD,zwxx.GZJY,qyxx.GSMC,qyxx.GSFL from
        V_RS_RLZY_ZWXX zwxx,V_RS_RLZY_QYXX qyxx where zwxx.RS_RLZY_QYXX_LSH = qyxx.RS_RLZY_QYXX_LSH)zwxxb
    </select>

    <select id="getZwxxList" resultType="com.zjrs.ggfw.rlzysc.dto.ZwxxListDTO">
        select a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,
        b.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,
        a.ZWMC zwmc,
        a.xz xz,
        nvl((select 1 from dual where a.xz in ('07','08','09','10','11','12','13','14')),'0') isgx,
        b.ZPDQ zpdq,
        a.GZJY
        gzjy,
        a.WHCD xl,
        a.ZPRS zprs,
        a.ts_xgsj fbsj,
        b.aab004 gsmc,
        b.GSFL gsfl,
        a.ZPFS zpfs
        from RS_RLZY_ZWXX a,RS_RLZY_QYXX b,
        v_rs_ggfw_user c, v_ab01 d
        where a.RS_RLZY_QYXX_LSH = b.RS_RLZY_QYXX_LSH
        and b.rs_yhlsh = c."USERID"
        and c.ztid = d.ztid
        <if test="dto.keyword != null and dto.keyword != ''">
            and (a.ZWMC LIKE '%'||#{dto.keyword}||'%' OR b.GSMC LIKE '%'||#{dto.keyword}||'%')
        </if>
        <if test="dto.isgx != null and dto.isgx != '' and dto.isgx == '1'.toString()">
            and a.xz in ('01','03','99')
        </if>
        <if test="dto.pxfs != null and dto.pxfs !=''">
            <choose>
                <when test="dto.pxfs == 1">
                    order by a.ts_xgsj desc
                </when>
                <when test="dto.pxfs == 2">
                    order by a.xz desc
                </when>
            </choose>
        </if>

    </select>

    <select id="getZwzntjList" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZwxxPO">
        select c.*,rownum from (select a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh, a.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,
        a.zwmc,a.gzdd,a.xz,a.gzjy,a.whcd, a.fldy, substr(a.ts_xgsj, 0, 10)  ts_xgsj, b.gsmc
        from rs_rlzy_zwxx a, rs_rlzy_qyxx b where
        a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        order by a.ts_xgsj desc)c where ROWNUM &lt; 6
    </select>

    <select id="getRgtjzw" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZwxxPO">
        select c.*,rownum from (select b.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh, b.rs_rlzy_qyxx_lsh
        rsrlzyqyxxlsh,b.zwmc,b.xz,b.gzdd,b.whcd,b.gzjy, c.gsmc, b.fldy, b.ts_xgsj tsxgsj
        from rs_rlzy_gwtj a,rs_rlzy_zwxx b, rs_rlzy_qyxx c where
        a.RS_RLZY_GWXX_LSH = b.rs_rlzy_zwxx_lsh
        and b.rs_rlzy_qyxx_lsh = c.rs_rlzy_qyxx_lsh
        order by b.ts_xgsj)c where rownum &lt; 6
    </select>

    <select id="getNoLoginZwtj" resultType="com.zjrs.ggfw.rlzysc.dto.ZwxxListDTO">
        select d.*,rownum from(select a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,b.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,a.zwmc,a.ts_xgsj
        fbsj,a.xz,b.zpdq zpdq,a.gzjy,a.whcd xl,a.fldy gsfl,b.gsmc
        from rs_rlzy_zwxx a,rs_rlzy_qyxx b where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        <if test="dto.pxfs != null and dto.pxfs !=''">
            <choose>
                <when test="dto.pxfs == 1">
                    order by a.ts_xgsj desc
                </when>
                <when test="dto.pxfs == 2">
                    order by a.xz desc
                </when>
            </choose>
        </if>
        )d where rownum &lt; 7
    </select>

    <select id="getNoLoginZwtjByaSlary" resultType="com.zjrs.ggfw.rlzysc.dto.ZwxxListDTO">
        select d.*,rownum from(select a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,b.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,a.zwmc,a.ts_xgsj
        fbsj,a.xz,b.zpdq zpdq,a.gzjy,a.whcd xl,a.fldy gsfl,b.gsmc
        from rs_rlzy_zwxx a,rs_rlzy_qyxx b where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        <if test="dto.pxfs != null and dto.pxfs !=''">
            <choose>
                <when test="dto.pxfs == 1">
                    order by a.ts_xgsj desc
                </when>
                <when test="dto.pxfs == 2">
                    and a.xz >= '07'
                    order by a.xz desc
                </when>
            </choose>
        </if>
        )d where rownum &lt; 7
    </select>

    <select id="getZwzpfs" resultType="com.zjrs.ggfw.rlzysc.dto.ZwxxListDTO">
        select a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,a.ZWMC zwmc,a.xz xz,b.ZPDQ zpdq,a.GZJY gzjy,a.WHCD xl,
        a.ZPRS zprs,a.ts_xgsj fbsj,b.GSMC gsmc,b.GSFL gsfl,a.ZPFS zpfs from RS_RLZY_ZWXX a,RS_RLZY_QYXX b
        where a.RS_RLZY_QYXX_LSH = b.RS_RLZY_QYXX_LSH 
        and a.RS_RLZY_QYXX_LSH = #{dto.rsrlzyqxxxlsh}
        order by a.ts_xgsj
    </select>

    <select id="getWlzphZw" resultType="com.zjrs.ggfw.rlzysc.dto.ZwxxListDTO">
        select a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,a.ZWMC zwmc,a.xz xz,c.ZPDQ zpdq,a.GZJY gzjy,a.WHCD xl,
        a.ZPRS zprs,a.ts_xgsj fbsj,c.GSMC gsmc,c.GSFL gsfl,a.ZPFS zpfs
        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.rsrlzyqxxxlsh}
        and a.rs_rlzy_zwxx_lsh = b.rs_rlzy_zwxx_lsh
    </select>

    <select id="getZyxxById" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZwxxPO">
        select a.*,b.czhm czhm,b.sjhm sjhm,b.dzyx qydzyx,b.gswz gswz from rs_rlzy_zwxx a,rs_rlzy_qyxx b
        where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh and a.rs_rlzy_zwxx_lsh = #{dto.rsrlzyzwxxlsh}
    </select>

    <select id="getXszw" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZwxxPO">
        select d.*,rownum from (select a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,b.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,a.zwmc zwmc,
        a.xz xz,b.zpdq zpdq,a.gzjy gzjy,a.whcd xl,
        a.ZPRS zprs,a.ts_xgsj fbsj,b.gsmc gsmc,b.gsfl gsfl,a.zpfs zpfs
        from RS_RLZY_ZWXX a,RS_RLZY_QYXX b
        where a.RS_RLZY_QYXX_LSH = b.RS_RLZY_QYXX_LSH
        )d where rownum &lt; 4
    </select>

    <select id="getNoLoginZwzntj" resultType="com.zjrs.ggfw.rlzysc.po.RsRlzyZwxxPO">
        select b.*,rownum from (select a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh, a.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,
        a.zwmc,a.gzdd,a.xz,a.gzjy,a.whcd from rs_rlzy_zwxx a  
        order by a.xz desc)b where rownum &lt; 6
    </select>
    <select id="getZwxxListForgr" resultType="com.zjrs.ggfw.rlzysc.dto.ZwxxListDTO">
        select a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,
        b.rs_rlzy_qyxx_lsh rsrlzyqyxxlsh,
        a.zwmc zwmc,
        a.xz xz,
        nvl((select 1 from dual where a.xz in ('07','08','09','10','11','12','13','14')),'0') isgx,
        b.zpdq zpdq,
        a.gzjy
        gzjy,
        a.whcd xl,
        a.zprs zprs,
        a.ts_xgsj fbsj,
        b.aab004 gsmc,
        b.gsfl gsfl,
        a.zpfs zpfs
        from rs_rlzy_zwxx a,rs_rlzy_qyxx b,
        v_rs_ggfw_user c, v_ab01 d
        where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and a.rs_rlzy_qyxx_lsh not in
        (select f.rs_rlzy_qyxx_lsh
        from v_rs_rlzy_pbsz e, v_rs_rlzy_qyxx f
        where e.rs_rlzy_qyxx_lsh = f.rs_rlzy_qyxx_lsh
        and e.rs_yhlsh = #{userid})
        and b.rs_yhlsh = c.userid
        and c.ztid = d.ztid
        and a.sftjnwsh = '1' and a.fbzt = '1' and a.zwzt = '1' and a.shjg = '1' and a.ts_yxbz = '1'
        and b.qyxx_shzt = '1'
        <if test="dto.keyword != null and dto.keyword != ''">
            and (a.ZWMC LIKE '%'||#{dto.keyword}||'%' OR b.GSMC LIKE '%'||#{dto.keyword}||'%')
        </if>
        <if test="dto.hylb != null and dto.hylb != ''">
            and a.ZPGZ like '%'||#{dto.hylb}||'%'
        </if>
        <if test="dto.zygz != null and dto.zygz != ''">
            and a.ZPGZ = #{dto.zygz}
        </if>
        <if test="dto.zpdq != null and dto.zpdq != ''">
            and b.zpdq = #{dto.zpdq}
        </if>
        <if test="dto.pxfs != null and dto.pxfs !=''">
            <choose>
                <when test="dto.pxfs == 1">
                    order by a.ts_xgsj desc
                </when>
                <when test="dto.pxfs == 2">
                    order by a.xz desc
                </when>
            </choose>
        </if>

    </select>
    <select id="getZwCntByZwmc" resultType="java.lang.Integer">
        SELECT count(zwmc) FROM v_rs_rlzy_zwxx a,v_rs_rlzy_qyxx b WHERE
        a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and b.rs_yhlsh = #{qyssr}
        AND a.zwmc like '%${ zwmc }%'
    </select>
</mapper>

