<?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.JlxxMapper">
    <select id="getTjjlList" resultType="com.zjrs.ggfw.rlzysc.dto.TjjlListDTO">
     select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",g.AAC003 as "xm",g.aac004 as "tsxb",
            a.ts_xl as "tsxl",c.qwzw as "tsqzlx",a.nl as "nl",c.gznx as "cjgzsj" from
            v_rs_rlzy_jlxx a,v_rs_ggfw_user b,v_rs_rlzy_qzyx c,v_rs_rlzy_qzyx_dyb d,v_ac01 g
          where a.RS_RLZY_JLXX_LSH = d.RS_RLZY_JLXX_LSH and c.RS_RLZY_QZYX_LSH = d.RS_RLZY_QZYX_LSH
          and a.TS_YXBZ = '02' and a.GKCD = '3' and a.SFTJNWSH = '02' 
          and a.RS_YHLSH = b.USERID
          and b.ZTID = g.ZTID
    </select>

    <select id="getDwTjjlList" resultType="com.zjrs.ggfw.rlzysc.dto.TjjlListDTO">
    select b.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",f.AAC003 as "xm",f.aac004 as "tsxb",
    b.ts_xl as "tsxl",c.QWHY as "tsqzlx",b.nl as "nl",b.cjgzsj as "cjgzsj" from v_rs_rlzy_qztj a,
    v_rs_rlzy_jlxx b,v_rs_rlzy_qzyx c,v_rs_rlzy_qzyx_dyb d,v_rs_ggfw_user e,v_ac01 f,v_rs_rlzy_qyxx g
    where a.RS_RLZY_JLXX_LSH = b.RS_RLZY_JLXX_LSH
    and b.rs_rlzy_jlxx_lsh = d.RS_RLZY_JLXX_LSH and c.RS_RLZY_QZYX_LSH = d.RS_RLZY_QZYX_LSH
    and a.RS_RLZY_QYXX_LSH = g.RS_RLZY_QYXX_LSH 
    and b.rs_yhlsh = e.USERID
    and a.rs_rlzy_qyxx_lsh = #{rsrlzyqyxxlsh}
    </select>

    <select id="getDwzpJlxxList" parameterType="com.zjrs.ggfw.rlzysc.dto.dwzp.DwzpJlListQueryDTO" resultType="HashMap">
        select a.rs_rlzy_jlgl_lsh "rsRlzyJlglLsh",a.rs_rlzy_jlxx_lsh "rsRlzyJlxxLsh",
        a.rs_rlzy_zwxx_lsh "rsRlzyZwxxLsh",a.rs_rlzy_qyxx_lsh "rsRlzyQyxxLsh",b.xm "xm",
        a.zt "zt",b.cjgzsj "cjgzsj",c.zwmc "zwmc",b.ts_xl "xl",e.qwxz "qwxz",b.ts_qzlx "qzlx",
        b.zjlx "zjlx",b.sfzhm "zjhm",b.ts_xb "xb",b.tx_tycc_lsh "txTyccid",b.sp_tycc_lsh "spTyccid",
        b.shjg "shjg",b.txshjg "txshjg",b.spshjg "spshjg"
        from v_rs_rlzy_jlgl a,v_rs_rlzy_jlxx b,v_rs_rlzy_zwxx c,v_rs_rlzy_qzyx_dyb d,v_rs_rlzy_qzyx e
        where a.rs_rlzy_jlxx_lsh=b.rs_rlzy_jlxx_lsh and a.rs_rlzy_zwxx_lsh=c.rs_rlzy_zwxx_lsh
        and b.rs_rlzy_jlxx_lsh=d.rs_rlzy_jlxx_lsh and d.rs_rlzy_qzyx_lsh=e.rs_rlzy_qzyx_lsh
        <if test="dto.zt != null and dto.zt != ''">
            and a.zt = #{dto.zt}
        </if>
        <if test="dto.qwxzz != null and dto.qwxzz != ''">
            and e.qwxz &lt;= #{dto.qwxzz}
        </if>
        order by a.ts_cjsj DESC
    </select>

    <select id="getJlxxList" resultType="com.zjrs.ggfw.rlzysc.dto.JLxxListDTO">
        select b.RS_RLZY_JLXX_LSH rsrlzyjlxxlsh,b.xm xm,b.nl nl,d.aac004 as "tsxb",b.TS_XL tsxl,b.CJGZSJ cjgzsj,
        c.QWZW qwzw,c.QWHY qwhy,c.GZNX gznx,c.GZDD qwdd,c.QWXZ qwxz from
        V_Rs_Rlzy_Qzyx_Dyb a,V_Rs_Rlzy_jlxx b,V_Rs_Rlzy_qzyx c,v_ac01 d,v_rs_ggfw_user e where
        a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh and a.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh
        and b.rs_yhlsh = e.userid and d.ztid = e.ztid
        <if test="dto.hylb != null and dto.hylb != ''">
            and c.qwhy = #{dto.hylb}
        </if>
        <if test="dto.zygz != null and dto.zygz != ''">
            and c.qwzw = #{dto.zygz}
        </if>
    </select>



    <select id="getJbxxglGrjbxx" parameterType="String" resultType="HashMap">

        select c.aac003 as "aac003",<!--姓名 -->
        c.aac058 as "aac058",<!--证件类型 -->
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC058' and aaa102= c.aac058) as "aac058name",
        c.aac147 as "aac147",<!--证件号码 -->
        c.aac161 as "aac161",<!--国家/地区代码 -->
        c.aac004 as "aac004",<!--性别 -->
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC004' and aaa102= c.aac004) as "aac004name",
        c.aac006 as "aac006",<!--出生日期 -->
        c.aac005 as "aac005",<!--民族 -->
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC005' and aaa102= c.aac005) as "aac005name",
        c.aac009 as "aac009",<!--户口性质 -->
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC009' and aaa102= c.aac009) as "aac009name",
        c.aac300 as "aac300",
        f_jbxxgl_getAllAreaName(c.aac300) as "aac300name"<!--户口所在地行政区划代码 -->
        <!--        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC300' and aaa102= c.aac300) as "aac300name"-->
        from rs_ggfw_user b, ac01 c
        where b.fwdx = '02'
        and b.ztid = c.ztid and b.userid = #{userid}
    </select>

    <select id="getJdjlxx" parameterType="String" resultType="HashMap">
        select RS_RLZY_JLXX_LSH as "rsrlzyjlxxlsh",SJHM as "sjhm",aac003 as "aac003",aac058 as "aac058",aac147 as "aac147",TS_XL as "tsxl",TS_RYLB as "tsrylb" from rs_rlzy_jlxx where ts_yxbz = '02' and sfjdjl = '02'
        and rs_yhlsh = #{userid}
    </select>

    <select id="getJljbxx" parameterType="map" resultType="HashMap">
        select
        c.jlmc as "jlmc",
        c.nl as "nl",
        c.xjzdxzqh as "xjzdxzqh",
        f_jbxxgl_getAllAreaName(c.xjzdxzqh) as "xjzdxzqhname",
        c.cjgzsj as "cjgzsj",
        c.ts_xl as "tsxl",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC011' and aaa102= c.ts_xl) as "tsxlname",
        c.ts_zzmm as "tszzmm",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC024' and aaa102= c.ts_zzmm) as "tszzmmname",
        c.ts_hyzk as "tshyzk",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC017' and aaa102= c.ts_hyzk) as "tshyzkname",
        c.yx as "yx",
        c.sjhm as "sjhm",
        c.yzbm as "yzbm",
        c.gddh as "gddh",
        c.xjzd as "xjzd",
        c.ts_qzlx as "tsqzlx",
        c.ts_byfl as "tsbyfl",
        c.sg as "sg",
        c.tz as "tz",
        c.slz as "slz",
        c.sly as "sly",
        c.jzdj as "jzdj",
        c.jzqdsj as "jzqdsj",
        c.xx as "xx",
        c.qq as "qq",
        c.ahtc as "ahtc",
        c.sfjszptj as "sfjszptj",
        c.gkcd as "gkcd",
        c.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",
        c.ts_xgsj as "tsxgsj",
        c.shjg as "shjg",
        c.ts_rylb as "tsrylb",
        c.tx_tycc_lsh as "txtycclsh",
        c.jl_tycc_lsh as "jltycclsh",
        c.jl_tycc_mc  as "jltyccmc",
        c.fbzt as "fbzt",
        c.gznx as "gznx",
        c.zyxx as "zyxx"
        from v_rs_rlzy_jlxx c
        where c.ts_yxbz = '02' and c.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and c.rs_yhlsh = #{map.userid}
    </select>

    <select id="getJljbxxAndJbxx" parameterType="map" resultType="HashMap">
        select
        c.jlmc as "jlmc",
        c.xjzdxzqh as "xjzdxzqh",
        f_jbxxgl_getAllAreaName(c.xjzdxzqh) as "xjzdxzqhname",
        c.nl as "nl",
        c.cjgzsj as "cjgzsj",
        c.ts_xl as "tsxl",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC011' and aaa102= c.ts_xl) as "tsxlname",
        c.ts_zzmm as "tszzmm",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC024' and aaa102= c.ts_zzmm) as "tszzmmname",
        c.ts_hyzk as "tshyzk",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC017' and aaa102= c.ts_hyzk) as "tshyzkname",
        c.yx as "yx",
        c.sjhm as "sjhm",
        c.yzbm as "yzbm",
        c.gddh as "gddh",
        c.xjzd as "xjzd",
        c.ts_qzlx as "tsqzlx",
        c.ts_byfl as "tsbyfl",
        c.sg as "sg",
        c.tz as "tz",
        c.slz as "slz",
        c.sly as "sly",
        c.jzdj as "jzdj",
        c.jzqdsj as "jzqdsj",
        c.xx as "xx",
        c.qq as "qq",
        c.ahtc as "ahtc",
        c.sfjszptj as "sfjszptj",
        c.gkcd as "gkcd",
        c.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",
        c.ts_xgsj as "tsxgsj",
        c.shjg as "shjg",
        c.aac003 as "aac003",
        c.aac006 as "aac006",
        c.aac058 as "aac058",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC058' and aaa102= c.aac058) as "aac058name",
        c.aac147 as "aac147",
        c.aac004 as "aac004",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC004' and aaa102= c.aac004) as "aac004name",
        c.aac005 as "aac005",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC005' and aaa102= c.aac005) as "aac005name",
        c.aac009 as "aac009",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC009' and aaa102= c.aac009) as "aac009name",
        c.aac300 as "aac300",
        f_jbxxgl_getAllAreaName(c.aac300) as "aac300name",
        c.ts_rylb as "tsrylb",
        c.tx_tycc_lsh as "txtycclsh",
        c.jl_tycc_lsh as "jltycclsh",
        c.jl_tycc_mc  as "jltyccmc",
        c.fbzt as "fbzt",
        c.gznx as "gznx",
        c.zyxx as "zyxx"
        from v_rs_rlzy_jlxx c
        where c.ts_yxbz = '02' and c.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and c.rs_yhlsh = #{map.userid}
    </select>

    <select id="getJljbxxForDw" parameterType="map" resultType="HashMap">
        select
        c.rs_yhlsh as "rsyhlsh",
        c.jlmc as "jlmc",
        c.nl as "nl",
        c.cjgzsj as "cjgzsj",
        c.ts_xl as "tsxl",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC011' and aaa102= c.ts_xl) as "tsxlname",
        c.ts_zzmm as "tszzmm",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC024' and aaa102= c.ts_zzmm) as "tszzmmname",
        c.ts_hyzk as "tshyzk",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC017' and aaa102= c.ts_hyzk) as "tshyzkname",
        c.yx as "yx",
        c.sjhm as "sjhm",
        c.yzbm as "yzbm",
        c.gddh as "gddh",
        c.xjzd as "xjzd",
        c.ts_qzlx as "tsqzlx",
        c.ts_byfl as "tsbyfl",
        c.sg as "sg",
        c.tz as "tz",
        c.slz as "slz",
        c.sly as "sly",
        c.jzdj as "jzdj",
        c.jzqdsj as "jzqdsj",
        c.xx as "xx",
        c.qq as "qq",
        c.ahtc as "ahtc",
        c.sfjszptj as "sfjszptj",
        c.gkcd as "gkcd",
        c.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",
        c.ts_xgsj as "tsxgsj",
        c.tx_tycc_lsh as "txtycclsh",
        c.sp_tycc_lsh as "sptycclsh",
        c.ts_rylb as "tsrylb",
        c.jl_tycc_lsh as "jltycclsh",
        c.jl_tycc_mc  as "jltyccmc",
        c.zyxx as "zyxx"
        from v_rs_rlzy_jlxx c
        where c.ts_yxbz = '02' and c.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
<!--        and rs_rg_jbxx_jlzt = '02'-->
    </select>

    <select id="getJlAndDwMszt" parameterType="map" resultType="Integer">
        select count(b.rs_rlzy_msgl_lsh) as "mszt"
        from v_rs_rlzy_qyxx a, v_rs_rlzy_msgl b
        where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        <if test="map.userid != null and map.userid != ''">
        and a.rs_yhlsh = #{map.userid}
        </if>
        and b.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
    </select>

    <select id="getjlyxbydyb" parameterType="Map" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",a.rs_rlzy_qzyx_lsh as "rsrlzyqzyxlsh"
        from v_rs_rlzy_qzyx_dyb a, v_rs_rlzy_jlxx b
        where b.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and b.rs_yhlsh = #{map.userid}
        and b.ts_yxbz = '02'
        and b.rs_rg_jbxx_jlzt = '02'
    </select>

    <select id="getJlqzyx" parameterType="Map" resultType="HashMap">
        select c.jlmc as "jlmc",
        a.qzzlx as "qzzlx",
        a.mqzk as "mqzk",
        a.gzdd as "gzdd",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_ZPDQ' and aaa102= a.gzdd) as "gzddname",
        a.gzxz as "gzxz",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_GZXZ' and aaa102= a.gzxz) as "gzxzname",
        a.qwxz as "qwxz",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_XZFW' and aaa102= a.qwxz) as "qwxzname",
        a.qwzw as "qwzw",
        (select aaa103 from  v_ggfw_dict where (aaa100 = 'RS_RLZY_EJZYFL' or aaa100 = 'RS_RLZY_ZYXX') and aaa102= a.qwzw) as "qwzwname",
        a.qwhy as "qwhy",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_YJZYFL' and aaa102= a.qwhy) as "qwhyname",
        a.gznx as "gznx",
        a.wyyz as "wyyz",
        strsplit2dict(a.wyyz, ',', 'RS_RLZY_WYYZ') as "wyyzname",
        a.wysp as "wysp",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_WYSP' and aaa102= a.wysp) as "wyspname",
        a.zwjs as "zwjs",
        b.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",
        a.rs_rlzy_qzyx_lsh as "rsrlzyqzyxlsh"
        from v_rs_rlzy_qzyx a,v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_jlxx c
        where a.rs_rlzy_qzyx_lsh = b.rs_rlzy_qzyx_lsh
        and c.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and b.rs_rlzy_jlxx_lsh = c.rs_rlzy_jlxx_lsh
<!--        and c.rs_yhlsh = #{map.userid}-->
        and c.ts_yxbz = '02'
<!--        and c.rs_rg_jbxx_jlzt = '02'-->
    </select>

    <select id="getJlqzyxForDw" parameterType="Map" resultType="HashMap">
        select c.jlmc as "jlmc",
        a.qzzlx as "qzzlx",
        a.mqzk as "mqzk",
        a.gzdd as "gzdd",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_ZPDQ' and aaa102= a.gzdd) as "gzddname",
        a.gzxz as "gzxz",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_GZXZ' and aaa102= a.gzxz) as "gzxzname",
        a.qwxz as "qwxz",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_XZFW' and aaa102= a.qwxz) as "qwxzname",
        a.qwzw as "qwzw",
        a.qwhy as "qwhy",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_YJZYFL' and aaa102= a.qwhy) as "qwhyname",
        a.gznx as "gznx",
        a.wyyz as "wyyz",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_WYYZ' and aaa102= a.wyyz) as "wyyzname",
        a.wysp as "wysp",
        (select aaa103 from  v_ggfw_dict where aaa100 = 'RS_RLZY_WYSP' and aaa102= a.wysp) as "wyspname",
        a.zwjs as "zwjs",
        b.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",
        a.rs_rlzy_qzyx_lsh as "rsrlzyqzyxlsh"
        from v_rs_rlzy_qzyx a,v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_jlxx c
        where a.rs_rlzy_qzyx_lsh = b.rs_rlzy_qzyx_lsh
        and c.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and b.rs_rlzy_jlxx_lsh = c.rs_rlzy_jlxx_lsh
        and c.ts_yxbz = '02'
<!--        and c.rs_rg_jbxx_jlzt = '02'-->
    </select>

    <select id="getJlgzjy" parameterType="Map" resultType="HashMap">
        select a.gzdw as "gzdw",
        a.szzw as "szzw",
        a.kssj as "mqzk",
        a.jssj as "gzdd",
        a.sshy as "gzxz",
        a.dwlb as "qwxz",
        a.jjlx as "qwzw",
        a.rygm as "qwhy",
        a.gzzz as "gznx",
        a.gzyj as "wyyz",
        a.rs_rlzy_gzjy_lsh as "rsrlzygzjylsh"
        from v_rs_rlzy_gzjy a
        where a.rs_rlzy_gzjy_lsh = #{map.rsrlzygzjylsh}
<!--        and a.rs_yhlsh = #{map.userid}-->
        and a.ts_yxbz = '02'
    </select>

    <select id="getgzjybydyb" parameterType="Map" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",a.rs_rlzy_gzjy_lsh as "rsrlzygzjylsh"
        from v_rs_rlzy_gzjy_dyb a, v_rs_rlzy_jlxx b, v_rs_rlzy_gzjy c
        where b.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and a.rs_rlzy_gzjy_lsh = c.rs_rlzy_gzjy_lsh
        and c.rs_rlzy_gzjy_lsh = #{map.rsrlzygzjylsh}
        and b.rs_yhlsh = #{map.userid}
        and b.ts_yxbz = '02'
        and b.rs_rg_jbxx_jlzt = '02'
    </select>

    <select id="getJljyjl" parameterType="Map" resultType="HashMap">
        select a.sfyhwjl as "sfyhwjl",
        a.byyx as "byyx",
        a.rxsj as "rxsj",
        a.bysj as "bysj",
        a.xl as "xl",
        a.sxzy as "sxzy",
        a.xw as "xw",
        a.xz as "xz",
        a.rs_rlzy_jyjl_lsh as "rsrlzyjyjllsh"
        from v_rs_rlzy_jyjl a
        where a.rs_rlzy_jyjl_lsh = #{map.rsrlzyjyjllsh}
<!--        and a.rs_yhlsh = #{map.userid}-->
        and a.ts_yxbz = '02'
    </select>

    <select id="getjyjlbydyb" parameterType="Map" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",a.rs_rlzy_jyjl_lsh as "rsrlzyjyjllsh"
        from v_rs_rlzy_jyjl_dyb a, v_rs_rlzy_jlxx b, v_rs_rlzy_jyjl c
        where b.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and a.rs_rlzy_jyjl_lsh = c.rs_rlzy_jyjl_lsh
        and c.rs_rlzy_jyjl_lsh = #{map.rsrlzyjyjllsh}
        and b.rs_yhlsh = #{map.userid}
        and b.ts_yxbz = '02'
        and b.rs_rg_jbxx_jlzt = '02'
    </select>

    <select id="getJlpxjl" parameterType="Map" resultType="HashMap">
        select a.pxjg as "pxjg",
        a.pxkc as "pxkc",
        a.kssj as "kssj",
        a.jssj as "jssj",
        a.zgzsh as "zgzsh",
        a.pxnr as "pxnr",
        a.rs_rlzy_pxjl_lsh as "rsrlzypxjllsh"
        from v_rs_rlzy_pxjl a
        where a.rs_rlzy_pxjl_lsh = #{map.rsrlzypxjllsh}
<!--        and a.rs_yhlsh = #{map.userid}-->
        and a.ts_yxbz = '02'
    </select>

    <select id="getpxjlbydyb" parameterType="Map" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",a.rs_rlzy_pxjl_lsh as "rsrlzypxjllsh"
        from v_rs_rlzy_pxjl_dyb a, v_rs_rlzy_jlxx b, v_rs_rlzy_pxjl c
        where b.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and a.rs_rlzy_pxjl_lsh = c.rs_rlzy_pxjl_lsh
        and c.rs_rlzy_pxjl_lsh = #{map.rsrlzypxjllsh}
        and b.rs_yhlsh = #{map.userid}
        and b.ts_yxbz = '02'
        and b.rs_rg_jbxx_jlzt = '02'
    </select>

    <select id="getJlzyjn" parameterType="Map" resultType="HashMap">
         select a.jnhztc as "jnhztc",
        a.zgdj as "zgdj",
        a.hqsj as "hqsj",
        a.cygz as "cygz",
        a.zsh as "zsh",
        a.csnx as "csnx",
        a.sm as "sm",
        a.rs_rlzy_zyjn_lsh as "rsrlzyzyjnlsh"
        from v_rs_rlzy_zyjn a
        where a.rs_rlzy_zyjn_lsh = #{map.rsrlzyzyjnlsh}
<!--        and a.rs_yhlsh = #{map.userid}-->
        and a.ts_yxbz = '02'
    </select>

    <select id="getzyjnbydyb" parameterType="Map" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",a.rs_rlzy_zyjn_lsh as "rsrlzyzyjnlsh"
        from v_rs_rlzy_zyjn_dyb a, v_rs_rlzy_jlxx b ,v_rs_rlzy_zyjn c
        where b.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and a.rs_rlzy_zyjn_lsh = c.rs_rlzy_zyjn_lsh
        and c.rs_rlzy_zyjn_lsh = #{map.rsrlzyzyjnlsh}
<!--        and b.rs_yhlsh = #{map.userid}-->
        and b.ts_yxbz = '02'
        and b.rs_rg_jbxx_jlzt = '02'
    </select>

    <select id="getJlshzs" parameterType="Map" resultType="HashMap">
        select a.zsmc as "zsmc",
        a.hqsj as "hqsj",
        a.zsbh as "zsbh",
        a.xxms as "xxms",
        a.rs_rlzy_shzs_lsh as "rsrlzyshzslsh"
        from v_rs_rlzy_shzs a
        where a.rs_rlzy_shzs_lsh = #{map.rsrlzyshzslsh}
<!--        and a.rs_yhlsh = #{map.userid}-->
        and a.ts_yxbz = '02'
    </select>

    <select id="getshzsbydyb" parameterType="Map" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",a.rs_rlzy_shzs_lsh as "rsrlzyshzslsh"
        from v_rs_rlzy_shzs_dyb a, v_rs_rlzy_jlxx b ,v_rs_rlzy_shzs c
        where b.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and a.rs_rlzy_shzs_lsh = c.rs_rlzy_shzs_lsh
        and c.rs_rlzy_shzs_lsh = #{map.rsrlzyshzslsh}
        and b.rs_yhlsh = #{map.userid}
        and b.ts_yxbz = '02'
        and b.rs_rg_jbxx_jlzt = '02'
    </select>

    <select id="getJlhjqk" parameterType="Map" resultType="HashMap">
        select a.zsmc as "zsmc",
        a.hqsj as "hqsj",
        a.bjjg as "bjjg",
        a.js as "js",
        a.cgmc as "cgmc",
        a.cgsm as "cgsm",
        a.qtsm as "qtsm",
        a.rs_rlzy_hjqk_lsh as "rsrlzyhjqklsh"
        from v_rs_rlzy_hjqk a
        where a.rs_rlzy_hjqk_lsh = #{map.rsrlzyhjqklsh}
<!--        and a.rs_yhlsh = #{map.userid}-->
        and a.ts_yxbz = '02'
    </select>

    <select id="gethjqkbydyb" parameterType="Map" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",a.rs_rlzy_hjqk_lsh as "rsrlzyhjqklsh"
        from v_rs_rlzy_hjqk_dyb a, v_rs_rlzy_jlxx b, v_rs_rlzy_hjqk c
        where b.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and a.rs_rlzy_hjqk_lsh = c.rs_rlzy_hjqk_lsh
        and c.rs_rlzy_hjqk_lsh = #{map.rsrlzyhjqklsh}
        and b.rs_yhlsh = #{map.userid}
        and b.ts_yxbz = '02'
        and b.rs_rg_jbxx_jlzt = '02'
    </select>

    <select id="getJlzcqk" parameterType="Map" resultType="HashMap">
          select a.zcmc as "zcmc",
        a.zcxl as "zcxl",
        a.hqsj as "hqsj",
        a.zgdj as "zgdj",
        a.pdfs as "pdfs",
        a.zcsm as "zcsm",
        a.rs_rlzy_zcqk_lsh as "rsrlzyzcqklsh"
        from v_rs_rlzy_zcqk a
        where a.rs_rlzy_zcqk_lsh = #{map.rsrlzyzcqklsh}
<!--        and a.rs_yhlsh = #{map.userid}-->
        and a.ts_yxbz = '02'
    </select>

    <select id="getzcqkbydyb" parameterType="Map" resultType="HashMap">
        select a.rs_rlzy_jlxx_lsh as "rsrlzyjlxxlsh",a.rs_rlzy_zcqk_lsh as "rsrlzyzcqklsh"
        from v_rs_rlzy_zcqk_dyb a, v_rs_rlzy_jlxx b, v_rs_rlzy_zcqk c
        where b.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
        and a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and a.rs_rlzy_zcqk_lsh = c.rs_rlzy_zcqk_lsh
        and c.rs_rlzy_zcqk_lsh = #{map.rsrlzyzcqklsh}
        and b.rs_yhlsh = #{map.userid}
        and b.ts_yxbz = '02'
        and b.rs_rg_jbxx_jlzt = '02'
    </select>

    <update id="updateMrToWmr" parameterType="Map">
        update v_rs_rlzy_jlxx set sfmrjl = '0' where
        sfmrjl = '02' and rs_yhlsh = #{map.userid}
    </update>

    <select id="getGrXm" parameterType="map" resultType="HashMap">
        select c.mc as "xm"
        from rs_ggfw_user c
        where c.userid = #{map.userid} and c.fwdx = '02'
    </select>

    <select id="getJlrgtjList" parameterType="String" resultType="HashMap">
        select p.aac003 as "xm",
        p.qwzw   as "qwzw",
        p.nl     as "nl",
        p.ts_xl  as "tsxl",
        p.gznx   as "gznx",
        p.aac004 as "xb"
        from (
        select v.*
        from (select c.aac003,
        c.aac004,
        a.*,
        b.gzdd,
        b.qwzw,
        b.gznx,
        b.qwxz,
        a.rs_yhlsh jlyhlsh,
        decode((select count(rs_rlzy_hmdgl_lsh)
        from v_rs_rlzy_hmdgl d
        where a.rs_yhlsh = d.rs_rlzy_ts_lsh
        and d.ts_yxbz = '02'),
        0,
        '0',
        '02') ishmd,
        (select f.rs_yhlsh dwyhlsh
        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 = a.rs_yhlsh) pbdwyhlsh
        from v_rs_rlzy_jlxx     a,
        v_rs_rlzy_qzyx     b,
        v_tyrz_user_grjbxx c,
        v_rs_rlzy_qzyx_dyb d
        where a.rs_rlzy_jlxx_lsh = d.rs_rlzy_jlxx_lsh
        and b.rs_rlzy_qzyx_lsh = d.rs_rlzy_qzyx_lsh
        and a.ts_yxbz = '02'
        and b.ts_yxbz = '02'
        and a.rs_yhlsh = c.useridcode) v
        where v.fbzt = '02'
        and v.ishmd = '0'
        and v.rs_rg_jbxx_jlzt = '02'
        and v.gkcd = '3') p,
        (select a.rs_rlzy_jlxx_lsh, b.rs_yhlsh
        from v_rs_rlzy_qztj a, v_rs_rlzy_qyxx b
        where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh) q
        where p.rs_rlzy_jlxx_lsh = q.rs_rlzy_jlxx_lsh
        and (p.pbdwyhlsh is null or p.pbdwyhlsh != q.rs_yhlsh)
        and q.rs_yhlsh = #{userid}
    </select>

    <select id="getJlzntjList" parameterType="String" resultType="HashMap">
        select v.aac003 as "xm",
        v.qwzw   as "qwzw",
        v.nl     as "nl",
        v.ts_xl  as "tsxl",
        v.gznx   as "gznx",
        v.aac004 as "xb"
        from v_rs_rlzy_jlxx_dsq_random v,v_rs_rlzy_qyxx q,v_rs_rlzy_zwxx p
        where v.fbzt = '02'
        and v.ishmd = '0'
        and v.rs_rg_jbxx_jlzt = '02'
        and v.gkcd = '3'
        and (v.pbdwyhlsh is null or v.pbdwyhlsh != q.rs_yhlsh)
        and p.rs_rlzy_qyxx_lsh = q.rs_rlzy_qyxx_lsh
        and q.rs_yhlsh = #{userid}
        and v.qwzw = p.zpgz
    </select>
    
    <select id="getMrjllsh" resultType="com.zjrs.ggfw.rlzysc.po.JlxxPO">
               select a.rs_rlzy_jlxx_lsh rsrlzyjlxxlsh,a.jlmc,a.ts_xgsj,a.ts_qzlx from
               rs_rlzy_jlxx a where a.rs_yhlsh = #{yhlsh} and a.sfmrjl = '02' and a.ts_yxbz = '02'
    </select>

    <select id="ajaxJltjsh" parameterType="HashMap" resultType="Integer">
        select count(a.rs_rlzy_jlxx_lsh) cnt
        from v_rs_rlzy_jlxx a, v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_qzyx c
        where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and b.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh
        and a.ts_yxbz = '02'
        and c.ts_yxbz = '02'
        and a.rs_rlzy_jlxx_lsh = #{map.rsrlzyjlxxlsh}
    </select>

    <select id="getMyJlxxMrlist" resultType="HashMap">
        select a.jlmc "jlmc",
        a.ts_qzlx "tsqzlx",
        a.ts_xgsj "tsxgsj",
        a.rs_rlzy_jlxx_lsh "rsrlzyjlxxlsh",
        a.rs_rg_jbxx_jlzt "rsrgjbxxjlzt",
        a.sftjnwsh "sftjnwsh",
        a.sfmrjl "sfmrjl",
        a.fbzt "fbzt",
        (select c.qwzw
        from v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_qzyx c
        where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and b.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh
        and c.ts_yxbz = '02') "qwzw"
        from v_rs_rlzy_jlxx a
        where a.ts_yxbz = '02'
        and a.sfmrjl = '02'
        <!--        and a.rs_rg_jbxx_jlzt = '02'-->
        and a.rs_yhlsh = #{userid}
    </select>

    <select id="getMyJlxxlist" resultType="HashMap">
        select a.jlmc "jlmc",
        a.ts_qzlx "tsqzlx",
        a.ts_xgsj "tsxgsj",
        a.rs_rlzy_jlxx_lsh "rsrlzyjlxxlsh",
        a.rs_rg_jbxx_jlzt "rsrgjbxxjlzt",
        a.sftjnwsh "sftjnwsh",
        a.sfmrjl "sfmrjl",
        a.fbzt "fbzt",
        a.shjg "shjg",
        (select c.qwzw
        from v_rs_rlzy_qzyx_dyb b, v_rs_rlzy_qzyx c
        where a.rs_rlzy_jlxx_lsh = b.rs_rlzy_jlxx_lsh
        and b.rs_rlzy_qzyx_lsh = c.rs_rlzy_qzyx_lsh
        and c.ts_yxbz = '02') "qwzw"
        from v_rs_rlzy_jlxx a
        where a.ts_yxbz = '02'
        and a.sfjdjl = '0'
        <!--        and a.rs_rg_jbxx_jlzt = '02'-->
        and a.rs_yhlsh = #{userid}
    </select>

    <select id="getzxJlxx" resultType="com.zjrs.ggfw.rlzysc.po.JlxxPO">
         select b.*,rownum from(select a.rs_rlzy_jlxx_lsh rsrlzyjlxxlsh,a.jlmc,a.ts_xgsj,a.ts_qzlx from
               rs_rlzy_jlxx a where a.rs_yhlsh = #{yhlsh} and a.ts_yxbz = '02'
               order by a.ts_xgsj desc)b where rownum &lt; 2
    </select>

    <select id="getGrzxJljbxx" parameterType="String" resultType="HashMap">
        select
        d.ts_xl,d.sjhm,d.qq,d.nl,d.shjg,d.shyj,
        c.aac003 as "aac003",<!--姓名 -->
        c.aac058 as "aac058",<!--证件类型 -->
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC058' and aaa102= c.aac058) as "aac058name",
        c.aac147 as "aac147",<!--证件号码 -->
        c.aac161 as "aac161",<!--国家/地区代码 -->
        c.aac004 as "aac004",<!--性别 -->
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC004' and aaa102= c.aac004) as "aac004name",
        c.aac006 as "aac006",<!--出生日期 -->
        c.aac005 as "aac005",<!--民族 -->
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC005' and aaa102= c.aac005) as "aac005name",
        c.aac009 as "aac009",<!--户口性质 -->
        (select aaa103 from  v_ggfw_dict where aaa100 = 'JBXX_AAC009' and aaa102= c.aac009) as "aac009name",
        c.aac300 as "aac300",
        f_jbxxgl_getAllAreaName(c.aac300) as "aac300name",<!--户口所在地行政区划代码 -->
        tx_tycc_lsh as "txtycclsh"
        from rs_ggfw_user b, ac01 c,rs_rlzy_jlxx d
        where b.fwdx = '02'
        and d.rs_yhlsh = b.userid
        and d.rs_rlzy_jlxx_lsh = #{rsrlzyjlxxlsh}
        and b.ztid = c.ztid and b.userid = #{userid}
    </select>
</mapper>
