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

    <select id="getJbxxglDwjbxx" parameterType="String" resultType="HashMap">
        select c.aab004 as "aab004",<!--公司名称-->
        c.aab998 as "aab998", <!--法人和其他组织统一社会信用代码-->
        c.aab078 as "aab078",<!--登记注册行政区划代码-->
        f_jbxxgl_getAllAreaName(c.aab078) as "aab078name",
        c.bze001 as "bze001", <!--登记地址-->
        c.aab019 as "aab019", <!--单位类型-->
        c.aab020 as "aab020", <!--经济类型-->
        c.aab021 as "aab021", <!--隶属关系-->
        c.aab022 as "aab022", <!--行业代码-->
        c.aae005 as "aae005", <!--联系电话-->
        c.aae004 as "aae004", <!--联系人-->
        c.aae006 as "aae006", <!--联系地址，单位地址-->
        c.aae007 as "aae007", <!--邮政编码-->
        c.aae159 as "aae159", <!--联系电子邮箱-->
        c.aab013 as "aab013", <!--法定代表人姓名-->
        c.aac058 as "aac058", <!--法定代表人身份证件类型-->
        c.aab014 as "aab014", <!--法定代表人公民身份号码-->
        c.baa120 as "baa120" <!--有效状态-->
        from rs_ggfw_user b, v_ab01 c
        where b.ztid = c.ztid
        and b.userid = #{userid}
    </select>

    <select id="getQyxx" parameterType="map" resultType="HashMap">
        select c.yyzz             as "yyzz",<!-- 营业执照-->
        c.gsbq             as "gsbq",<!-- 公司标签-->
        c.gsgm             as "gsgm",<!-- 公司规模-->
        c.gswz             as "gswz",<!-- 公司网站-->
        c.zpdq             as "zpdq",<!-- 招聘地区-->
        c.clrq             as "clrq",<!-- 成立日期-->
        c.dzyx             as "dzyx",<!-- 联系电子邮箱-->
        c.yzbm             as "yzbm",<!-- 邮政编码-->
        c.dtbz             as "dtbz",<!-- 地图标注-->
        c.czhm             as "czhm",<!-- 传真号码-->
        c.gsjs             as "gsjs",<!-- 公司介绍-->
        c.gsfl             as "gsfl",<!-- 公司福利-->
        c.gsxz             as "gsxz",<!-- 公司性质-->
        c.zczb             as "zczb",<!-- 注册资本-->
        c.sjhm             as "sjhm",<!-- 手机号码-->
        c.qyxx_shzt       as "qyxxshzt",<!-- 企业信息审核状态-->
        c.sftjsh        as "sftjsh",<!-- 是否提交审核-->
        c.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",<!-- 主键-->
        c.hrmc as "hrmc",
        c.hrxb as "hrxb",
        c.hrzjhm as "hrzjhm",
        c.hrlxfs as "hrlxfs",
        c.rs_yhlsh as "rsyhlsh"
        from v_rs_rlzy_qyxx c
        where 1=1
        <if test="map.rsrlzyqyxxlsh != null and map.rsrlzyqyxxlsh != ''">
            and c.rs_rlzy_qyxx_lsh = #{map.rsrlzyqyxxlsh}
        </if>
    </select>

    <select id="getQyxxAndJbxx" parameterType="map" resultType="HashMap">
        select c.yyzz             as "yyzz",<!-- 营业执照-->
        c.gsbq             as "gsbq",<!-- 公司标签-->
        c.gsgm             as "gsgm",<!-- 公司规模-->
        c.gswz             as "gswz",<!-- 公司网站-->
        c.zpdq             as "zpdq",<!-- 招聘地区-->
        c.clrq             as "clrq",<!-- 成立日期-->
        c.dzyx             as "dzyx",<!-- 联系电子邮箱-->
        c.yzbm             as "yzbm",<!-- 邮政编码-->
        c.dtbz             as "dtbz",<!-- 地图标注-->
        c.czhm             as "czhm",<!-- 传真号码-->
        c.gsjs             as "gsjs",<!-- 公司介绍-->
        c.gsfl             as "gsfl",<!-- 公司福利-->
        c.gsxz             as "gsxz",<!-- 公司性质-->
        c.zczb             as "zczb",<!-- 注册资本-->
        c.sjhm             as "sjhm",<!-- 手机号码-->
        c.qyxx_shzt       as "qyxxshzt",<!-- 企业信息审核状态-->
        c.sftjsh        as "sftjsh",<!-- 是否提交审核-->
        c.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",<!-- 主键-->
        c.aab004 as "aab004",
        c.aab998 as "aab998",
        c.aae006 as "aae006",
        c.bze001 as "bze001",
        f_jbxxgl_getAllAreaName(c.aab078) as "aab078name",
        c.aab078 as "aab078",
        c.aab019 as "aab019",
        c.aab020 as "aab020",
        c.aab021 as "aab021",
        c.aab022 as "aab022",
        c.aae004 as "aae004",
        c.aae005 as "aae005",
        c.baa120 as "baa120",
        c.aab013 as "aab013",
        c.aac058 as "aac058",
        c.aab014 as "aab014",
        c.hrmc as "hrmc",
        c.hrxb as "hrxb",
        c.hrzjhm as "hrzjhm",
        c.hrlxfs as "hrlxfs"
        from v_rs_rlzy_qyxx c
        where 1=1
        <if test="map.rsrlzyqyxxlsh != null and map.rsrlzyqyxxlsh != ''">
            and c.rs_rlzy_qyxx_lsh = #{map.rsrlzyqyxxlsh}
        </if>
    </select>

    <select id="getSyQyxx" parameterType="map" resultType="HashMap">
        select nvl(c.aab004, c.gsmc) as "gsmc",<!--公司名称-->
        (select aaa103 from v_ggfw_dict where aaa100 = 'JBXX_AAB020'
        and aaa102 = (select d.aab020 from rs_ggfw_user b, v_ab01 d
        where b.fwdx = '2'
        and b.ztid = d.ztid
        and b.userid = c.rs_yhlsh )) as "jjlx",<!--公司性质-->
        c.gsgm as "gsgm",<!--公司规模-->
        c.clrq as "clrq",<!--成立日期-->
        (select d.aab013 from rs_ggfw_user b, v_ab01 d
        where b.fwdx = '02'
        and b.ztid = d.ztid
        and b.userid = c.rs_yhlsh ) as "frxm",<!--法人姓名-->
        c.ts_xgsj as "tsxgsj",<!--更新时间-->
        c.zczb as "zczb",<!--注册资本-->
        c.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",
        c.hrmc as "hrmc",
        c.hrxb as "hrxb",
        c.hrzjhm as "hrzjhm",
        c.hrlxfs as "hrlxfs"
        from v_rs_rlzy_qyxx c
        where c.rs_yhlsh = #{map.userid}
        <if test="map.rsrlzyqyxxlsh != null and map.rsrlzyqyxxlsh != ''">
            and c.rs_rlzy_qyxx_lsh = #{map.rsrlzyqyxxlsh}
        </if>
    </select>

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

    <select id="getYdzphs" parameterType="map" resultType="HashMap">
        select count(distinct a.rs_rlzy_zphgl_lsh) as "ydzphs"
        from v_rs_rlzy_zphgl a,v_rs_rlzy_hcgl b,v_rs_rlzy_zwgl c,v_rs_rlzy_zwwzyysh d,v_rs_rlzy_qyxx f
        where a.rs_rlzy_zphgl_lsh = b.rs_rlzy_zphgl_lsh
        and b.rs_rlzy_hcgl_lsh = c.rs_rlzy_hcgl_lsh
        and c.rs_rlzy_zwgl_lsh = d.rs_rlzy_zwgl_lsh
        and f.rs_rlzy_qyxx_lsh = d.rs_rlzy_qyxx_lsh
        and f.rs_yhlsh = #{map.userid}
    </select>

    <select id="getFbgws" parameterType="map" resultType="HashMap">
        select count(distinct rs_rlzy_zwxx_lsh) as "fbgws"
        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 = #{map.userid}
    </select>

    <select id="getScjls" parameterType="map" resultType="HashMap">
        select count(distinct a.rs_rlzy_scgz_lsh) as "scjls"
        from v_rs_rlzy_scgz a,v_rs_rlzy_qyxx b,v_rs_rlzy_jlxx c
        where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and b.rs_yhlsh = #{map.userid} 
    </select>
    <select id="getMapQy" resultType="HashMap" parameterType="com.zjrs.ggfw.rlzysc.dto.grzxjlxx.ZjDTO">
        select a.rs_rlzy_zwxx_lsh as "rsrlzyzwxxlsh",
               c.aab004 as "aab004",
               c.aae004 as "aae004",
               c.aae005 as "aae005",
               c.aae006 as "aae006",
               a.zwmc as "zwmc"
        from v_rs_rlzy_zwxx a, v_rs_rlzy_qyxx b, v_tyrz_user_dwjbxx c
        where b.rs_yhlsh = c.useridcode(+)
        and b.rs_rlzy_qyxx_lsh = #{dto.zjlsh}
        and a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh

    </select>
    <select id="getMapZw" resultType="HashMap" parameterType="com.zjrs.ggfw.rlzysc.dto.grzxjlxx.SyMapQyQueryDTO">
        select v.zwmc as "zwmc",
        v.lng as "lng",
        v.lat as "lat",
        v.rs_rlzy_qyxx_lsh as "rsrlzyqyxxlsh",
        v.rsrlzyzwxxlsh as "rsrlzyzwxxlsh",
        (select aaa103
        from v_ggfw_dict
        where aaa100 = 'RS_RLZY_XZFW'
        and aaa102 = v.xz) as "xz",
        (select aaa103
        from v_ggfw_dict
        where aaa100 = 'RS_RLZY_GZJY'
        and aaa102 = v.gzjy) as "gzjy",
        (select aaa103
        from v_ggfw_dict
        where aaa100 = 'RS_RLZY_XL'
        and aaa102 = v.whcd) as "whcd",
        to_char(to_date(v.tsxgsj, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') as "tsxgsj",
        (select aaa103
        from v_ggfw_dict
        where aaa100 = 'RS_RLZY_ZPDQ'
        and aaa102 = v.zpdq) as "zpdq",
        v.gsmc as "gsmc"
        from (select a.zwmc,
        a.xz,
        a.gzjy,
        a.whcd,
        a.ts_xgsj tsxgsj,
        a.rs_rlzy_zwxx_lsh rsrlzyzwxxlsh,
        b.zpdq,
        b.lng,
        b.lat,
        b.rs_rlzy_qyxx_lsh,
        c.aab004 gsmc,
        c.aab022 sshy,
        a.zpgz,
        decode((select count(rs_rlzy_hmdgl_lsh)
        from v_rs_rlzy_hmdgl c
        where b.rs_yhlsh = c.rs_rlzy_ts_lsh
        and c.ts_yxbz = '1'),
        0,
        '0',
        '1') ishmd
        from v_rs_rlzy_zwxx a, v_rs_rlzy_qyxx b, v_tyrz_user_dwjbxx c
        where a.rs_rlzy_qyxx_lsh = b.rs_rlzy_qyxx_lsh
        and b.rs_yhlsh = c.useridcode(+)) v
        where v.ishmd = '0'
        <if test="dto.zpdq != null and dto.zpdq != ''">
            and v.zpdq = #{dto.zpdq}
        </if>
    </select>

    <select id="validateQyxx" parameterType="com.zjrs.ggfw.rlzysc.dto.QyxxDTO" resultType="HashMap">
        select * from (select * from v_rs_rlzy_qyxx where aab004 = #{dto.aab004}
        or aab998 = #{dto.aab998}) where rownum &lt;= 1
    </select>

</mapper>
