<?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.chinaweal.aiccs.aiccs.susnate.mapper.ESusNateOperRegMapper">

    <resultMap id="EntBaseWithSusNateOperRegVOMap" type="com.chinaweal.aiccs.aiccs.susnate.vo.EntBaseWithSusNateOperRegVO">
        <id property="pripid" column="pripid"/>
        <result property="uniscid" column="uniscid"/>
        <result property="regno" column="regno"/>
        <result property="entname" column="entname"/>
        <result property="enttype" column="enttype"/>
        <result property="enttypeCn" column="enttypeCn"/>
        <result property="industryphy" column="industryphy"/>
        <result property="industryco" column="industryco"/>
        <result property="estdate" column="estdate"/>
        <result property="regorg" column="regorg"/>
        <result property="regorgCn" column="regorgCn"/>
        <result property="opscope" column="opscope"/>
        <result property="opfrom" column="opfrom"/>
        <result property="opto" column="opto"/>
        <result property="regstate" column="regstate"/>
        <result property="regstateCn" column="regstateCn"/>
        <result property="dom" column="dom"/>
        <result property="regcap" column="regcap"/>
        <result property="regcapcur" column="regcapcur"/>
        <result property="regcapcurCn" column="regcapcurCn"/>
        <result property="name" column="name"/>
        <result property="apprdate" column="apprdate"/>
        <result property="suporg" column="suporg"/>
        <result property="suporgCn" column="suporgCn"/>
        <result property="regulatory" column="regulatory"/>
        <result property="regulatoryName" column="regulatoryName"/>
        <result property="hasSusNateOperReg" column="hasSusNateOperReg"/>
    </resultMap>

    <resultMap id="NateOperCombinedVOMap" type="com.chinaweal.aiccs.aiccs.susnate.vo.NateOperCombinedVO">
        <id property="id" column="personateId"/>
        <result property="pripid" column="pripid"/>
        <result property="entname" column="entname"/>
        <result property="uniscid" column="uniscid"/>
        <result property="regno" column="regno"/>
        <result property="regOrg" column="regOrg"/>
        <result property="regOrgCn" column="regOrgCn"/>
        <result property="judAuth" column="judAuth"/>
        <result property="judAuthCn" column="judAuthCn"/>
        <result property="judDate" column="judDate"/>
        <result property="regTime" column="regTime"/>
        <result property="regItem" column="regItem"/>
        <result property="tel" column="tel"/>
        <result property="noticeFrom" column="noticeFrom"/>
        <result property="noticeTo" column="noticeTo"/>
        <result property="name" column="name"/>
        <result property="handlRest" column="handlRest"/>
        <result property="handlRea" column="handlRea"/>
        <result property="handlReaCn" column="handlReaCn"/>
        <result property="noticeContent" column="noticeContent"/>
        <result property="noticeDate" column="noticeDate"/>
    </resultMap>

    <select id="selectESusNateVoList" resultType="com.chinaweal.aiccs.aiccs.susnate.vo.ESusNateVo">
        select
        eso.PERSONATEID personateId, eso.REGORG regOrg, eso.REGORG_CN regOrgCn, eso.PRIPID pripid,
        eso.JUDAUTH judAuth, eso.JUDAUTH_CN judAuthCn, eso.HANDLREA handlRea, eso.HANDLREACN handlReaCn,
        eso.UNISCID uniscid, eso.REGNO regno, eso.ENTNAME entname, eso.NAME name, eso.TEL tel,
        eso.NOTICEFROM noticeFrom, eso.NOTICETO noticeTo, eso.JUDDATE judDate, eso.REGITEM regItem,
        eso.REGTIME regTime, eso.HANDLREST handlRest, eso.dstate dstate,
        efi.FALPERID falperId , efi.NAME resName, efi.CERTYPE cerType, efi.CERNO cerNo,
        eno.PERSONATERESID personatresId, eno.NOTICECONTENT noticeContent, eno.NOTICEDATE noticeDate
        from e_susnate_operreg eso
        left join e_falper_info efi on eso.PERSONATEID = efi.FALPERID
        left join e_nate_operregdec eno on eso.PERSONATEID = eno.PERSONATERESID
        <where>
            eso.dstate != '2'
            <if test="param.searchUscc != null and param.searchUscc != ''">
                and (eso.UNISCID = #{param.searchUscc} or eso.REGNO = #{param.searchUscc})
            </if>
        </where>
    </select>

    <select id="listEntBaseWithSusNateOperReg" resultMap="EntBaseWithSusNateOperRegVOMap">
        SELECT
            eb.PRIPID AS pripid,
            eb.UNISCID AS uniscid,
            eb.REGNO AS regno,
            eb.ENTNAME AS entname,
            eb.ENTTYPE AS enttype,
            eb.ENTTYPE_CN AS enttypeCn,
            eb.INDUSTRYPHY AS industryphy,
            eb.INDUSTRYCO AS industryco,
            eb.ESTDATE AS estdate,
            eb.REGORG AS regorg,
            eb.REGORG_CN AS regorgCn,
            eb.OPSCOPE AS opscope,
            eb.OPFROM AS opfrom,
            eb.OPTO AS opto,
            eb.REGSTATE AS regstate,
            eb.REGSTATE_CN AS regstateCn,
            eb.DOM AS dom,
            eb.REGCAP AS regcap,
            eb.REGCAPCUR AS regcapcur,
            eb.REGCAPCUR_CN AS regcapcurCn,
            eb.NAME AS name,
            eb.APPRDATE AS apprdate,
            eb.suporg AS suporg,
            eb.suporg_cn AS suporgCn,
            eb.regulatory AS regulatory,
            eb.regulatory_name AS regulatoryName,
            CASE
                WHEN EXISTS (
                    SELECT 1 FROM e_susnate_operreg es
                    WHERE es.PRIPID = eb.PRIPID
                    AND es.dstate != '2'
                ) THEN '1'
                ELSE '0'
            END AS hasSusNateOperReg
        FROM e_baseinfo eb
        <where>
            <if test="query.hasSusNateOperReg != null and query.hasSusNateOperReg != ''">
                AND (
                    <choose>
                        <when test="query.hasSusNateOperReg == '1'">
                            EXISTS (
                                SELECT 1 FROM e_susnate_operreg es
                                WHERE es.PRIPID = eb.PRIPID
                                AND es.dstate != '2'
                            )
                        </when>
                        <when test="query.hasSusNateOperReg == '0'">
                            NOT EXISTS (
                                SELECT 1 FROM e_susnate_operreg es
                                WHERE es.PRIPID = eb.PRIPID
                                AND es.dstate != '2'
                            )
                        </when>
                    </choose>
                )
            </if>
            <if test="query.uniscid != null and query.uniscid != ''">
                AND eb.UNISCID = #{query.uniscid}
            </if>
            <if test="query.regno != null and query.regno != ''">
                AND eb.REGNO = #{query.regno}
            </if>
            <if test="query.entname != null and query.entname != ''">
                AND eb.ENTNAME LIKE CONCAT('%', #{query.entname}, '%')
            </if>
            <if test="query.searchUscc != null and query.searchUscc != ''">
                AND (eb.UNISCID = #{query.searchUscc} OR eb.REGNO = #{query.searchUscc})
            </if>
        </where>
        ORDER BY eb.PRIPID
    </select>

    <select id="listNateOperCombined" resultMap="NateOperCombinedVOMap">
        SELECT
            eso.PERSONATEID AS personateId,
            eso.PRIPID AS pripid,
            eso.ENTNAME AS entname,
            eso.UNISCID AS uniscid,
            eso.REGNO AS regno,
            eso.REGORG AS regOrg,
            eso.REGORG_CN AS regOrgCn,
            eso.JUDAUTH AS judAuth,
            eso.JUDAUTH_CN AS judAuthCn,
            eso.JUDDATE AS judDate,
            eso.REGTIME AS regTime,
            eso.REGITEM AS regItem,
            eso.TEL AS tel,
            eso.NOTICEFROM AS noticeFrom,
            eso.NOTICETO AS noticeTo,
            eso.NAME AS name,
            eso.HANDLREST AS handlRest,
            eso.HANDLREA AS handlRea,
            eso.HANDLREACN AS handlReaCn,
            eno.NOTICECONTENT AS noticeContent,
            eno.NOTICEDATE AS noticeDate
        FROM e_susnate_operreg eso
        LEFT JOIN e_nate_operregdec eno ON eso.PERSONATEID = eno.PERSONATERESID
        <where>
            eso.dstate != '2'
            <if test="query.id != null and query.id != ''">
                AND eso.PERSONATEID = #{query.id}
            </if>
            <if test="query.pripid != null and query.pripid != ''">
                AND eso.PRIPID = #{query.pripid}
            </if>
            <if test="query.entname != null and query.entname != ''">
                AND eso.ENTNAME LIKE CONCAT('%', #{query.entname}, '%')
            </if>
            <if test="query.uniscid != null and query.uniscid != ''">
                AND eso.UNISCID = #{query.uniscid}
            </if>
            <if test="query.regno != null and query.regno != ''">
                AND eso.REGNO = #{query.regno}
            </if>
            <if test="query.regOrg != null and query.regOrg != ''">
                AND eso.REGORG = #{query.regOrg}
            </if>
            <if test="query.regItem != null and query.regItem != ''">
                AND eso.REGITEM = #{query.regItem}
            </if>
            <if test="query.name != null and query.name != ''">
                AND eso.NAME = #{query.name}
            </if>
            <if test="query.handlRea != null and query.handlRea != ''">
                AND eso.HANDLREA = #{query.handlRea}
            </if>
            <if test="query.noticeContent != null and query.noticeContent != ''">
                AND eno.NOTICECONTENT LIKE CONCAT('%', #{query.noticeContent}, '%')
            </if>
            <if test="query.judDateStart != null and query.judDateStart != ''">
                AND eso.JUDDATE >= #{query.judDateStart}
            </if>
            <if test="query.judDateEnd != null and query.judDateEnd != ''">
                AND eso.JUDDATE &lt;= #{query.judDateEnd}
            </if>
            <if test="query.regTimeStart != null and query.regTimeStart != ''">
                AND eso.REGTIME >= #{query.regTimeStart}
            </if>
            <if test="query.regTimeEnd != null and query.regTimeEnd != ''">
                AND eso.REGTIME &lt;= #{query.regTimeEnd}
            </if>
            <if test="query.noticeDateStart != null and query.noticeDateStart != ''">
                AND eno.NOTICEDATE >= #{query.noticeDateStart}
            </if>
            <if test="query.noticeDateEnd != null and query.noticeDateEnd != ''">
                AND eno.NOTICEDATE &lt;= #{query.noticeDateEnd}
            </if>
        </where>
        ORDER BY eso.PERSONATEID DESC
    </select>

</mapper>
