<?xml version="1.0" encoding="GBK" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!-- Always ensure to use the correct XML header as above! -->
<mapper namespace="com.chinaweal.aiccs.aiccs.query.mapper.TSEntBaseQueryMapper">
    <resultMap id="baseMap" type="com.chinaweal.aiccs.aiccs.query.entity.TSEntBaseQuery" >
        <result property="priPID" column="priPID" jdbcType="CHAR"/>
        <result property="regNO" column="regNO" jdbcType="VARCHAR"/>
        <result property="entName" column="entName" jdbcType="VARCHAR"/>
        <result property="entType" column="entType" jdbcType="CHAR"/>
        <result property="entTypeName" column="entTypeName" jdbcType="VARCHAR"/>
        <result property="subEntType" column="subEntType" jdbcType="CHAR"/>
        <result property="industryPhy" column="industryPhy" jdbcType="CHAR"/>
        <result property="industryCo" column="industryCo" jdbcType="CHAR"/>
        <result property="foundDate" column="estdate" jdbcType="TIMESTAMP"/>
        <result property="regAicID" column="aicID" jdbcType="CHAR"/>
        <result property="insObjType" column="insObjType" jdbcType="CHAR"/>
        <result property="insObjTypeName" column="insObjTypeName" jdbcType="VARCHAR"/>
        <result property="status" column="entstate" jdbcType="CHAR"/>
        <result property="leRepName" column="leRepName" jdbcType="VARCHAR"/>
        <result property="levelType" column="levelType" jdbcType="CHAR"/>
        <result property="lauptime" column="laUpTime" jdbcType="TIMESTAMP"/>

        <!-- after needs-->
        <result property="typeList" column="typeList" jdbcType="VARCHAR"/>
        <result property="address" column="address" jdbcType="VARCHAR"/>
        <result property="areaName" column="areaName" jdbcType="VARCHAR"/>
        <result property="areaID" column="areaID" jdbcType="CHAR"/>
        <result property="entBaseAddrID" column="entBaseAddrID" jdbcType="CHAR"/>
        <result property="realBusiness" column="realBusiness" jdbcType="VARCHAR"/>
        <result property="business" column="opscope" jdbcType="VARCHAR"/>
        <result property="groupName" column="groupName" jdbcType="VARCHAR"/>
        <result property="groupID" column="groupID" jdbcType="CHAR"/>
        <result property="foundEndDate" column="foundEndDate" jdbcType="VARCHAR"/>
        <result property="regCap" column="regCap" jdbcType="NUMERIC"/>
        <result property="opFrom" column="opFrom" jdbcType="TIMESTAMP"/>
        <result property="opTo" column="opTo" jdbcType="TIMESTAMP"/>
        <result property="marketID" column="marketID" jdbcType="CHAR"/>
        <result property="marketName" column="marketName" jdbcType="VARCHAR"/>
        <result property="localAdm" column="localAdm" jdbcType="VARCHAR"/>
        <result property="industryName" column="industryName" jdbcType="VARCHAR"/>
        <result property="importAreaName" column="importAreaName" jdbcType="VARCHAR"/>
        <result property="hasArea" column="hasArea" jdbcType="VARCHAR"/>
        <result property="apprDate" column="apprDate" jdbcType="VARCHAR"/>
        <result property="lastInspectDate" column="lastInspectDate" jdbcType="VARCHAR"/>
        <result property="liaName" column="liaName" jdbcType="VARCHAR"/>
        <result property="mobileTel" column="mobileTel" jdbcType="VARCHAR"/>
        <result property="registdate" column="registdate" jdbcType="VARCHAR"/>

    </resultMap>

     <!--列入异常名录、协助执行列入严重违法失信 主体查询-->
     <select id="selectByPageExptList" resultMap="baseMap" parameterType="com.chinaweal.aiccs.aiccs.query.entity.TSEntBaseQuery">
        select distinct teb.priPID, teb.entName,teb.estdate,teb.entType,
          (case when teb.USCC = null then teb.regNO else teb.USCC end) as regNO
        from TREntbase teb
        left join TSSupEntBase tseb on teb.priPID=tseb.priPID
        <if test="(entBase.business != null and entBase.business != '') or (entBase.opFrom != null and entBase.opFrom != '') or (entBase.opTo != null and entBase.opTo != '') ">
            left join TREntBaseAppend teba2 on teb.priPID=teba2.priPID
        </if>
        left join TSSupEntAddr teba on teb.priPID=teba.priPID
        <if test="entBase.groupID != null and entBase.groupID != ''">
            left join TSArea ta on teba.areaID=ta.areaID
            left join TSGroup tg on ta.groupID=tg.groupID
        </if>
        <if test="entBase.industryName != null and entBase.industryName != ''">
            left join TSEntIndustry tei on teb.priPID=tei.priPID
            left join TSHighIndustry thi on tei.highIndustryID=thi.highIndustryID
        </if>
        <if test="entBase.importAreaName != null and entBase.importAreaName != ''">
            left join TSEntArea tea on teb.priPID=tea.priPID
            left join TSImportantArea tia on tea.areaID=tia.areaID
        </if>
        where teb.entstate='1'
        <if test="entBase.enterType != null and entBase.enterType !=''">
            <choose>
                <when test="entBase.enterType.equals('farmer')">
                    and teb.enttype  in ('9100','9200' )
                </when>
                <when test="entBase.enterType.equals('individual')">
                    and teb.enttype  in ('9910')
                </when>
                <otherwise>
                    and teb.enttype not in ( '9100','9200','9910')
                </otherwise>
            </choose>
        </if>
        <if test="entBase.regNO != null and entBase.regNO !=''">
            <choose>
                <when test="entBase.regNO.trim().length() == 18">
                  AND teb.USCC=#{entBase.regNO}
                </when>
                <otherwise>
                  AND teb.regNO=#{entBase.regNO}
                </otherwise>
            </choose>
        </if>
        <if test="entBase.entName != null and entBase.entName != ''">
            AND teb.entName like '%'||#{entBase.entName}|| '%'
        </if>
        <if test="entBase.typeList != null and entBase.typeList != '' ">
            AND teb.subEntType in (${entBase.typeList})
        </if>
        <if test="entBase.levelType != null and entBase.levelType != ''">
            AND tseb.levelType = #{entBase.levelType}
        </if>
        <if test="entBase.address != null and entBase.address != ''">
            AND teba.address like '%'||#{entBase.address}|| '%'
        </if>
        <if test="entBase.leRepName != null and entBase.leRepName != ''">
            AND teb.leRepName like '%'||#{entBase.leRepName}|| '%'
        </if>
        <!--<if test="entBase.regAicID != null and entBase.regAicID != ''">
            AND teb.aicID =#{entBase.regAicID}
        </if>-->
        <if test="entBase.insObjType != null and entBase.insObjType != ''">
            AND tseb.insObjType =#{entBase.insObjType}
        </if>
        <if test="entBase.foundDate != null and entBase.foundDate != '' ">
            AND teb.estdate &gt;= #{entBase.foundDate}
        </if>
        <if test="entBase.foundEndDate != null and entBase.foundEndDate != '' ">
            AND teb.estdate &lt; DATEADD(day,1,#{entBase.foundEndDate})
        </if>
        <if test="entBase.status != null and entBase.status != ''">
            AND teb.entstate =#{entBase.status}
        </if>
        <if test="entBase.groupID != null and entBase.groupID != ''">
            AND tg.groupID =#{entBase.groupID}
        </if>
        <if test="entBase.areaID != null and entBase.areaID != ''">
            AND teba.areaID =#{entBase.areaID}
        </if>
        <if test="entBase.business != null and entBase.business != ''">
            AND teba2.opscope like '%'||#{entBase.business}||'%'
        </if>
        <if test="entBase.regCap != null and entBase.regCap != ''">
            AND teb.regCap =#{entBase.regCap}
        </if>
        <if test="entBase.opFrom != null and entBase.opFrom != '' ">
            AND teba2.opFrom &gt;= #{entBase.opFrom}
        </if>
        <if test="entBase.opTo != null and entBase.opTo != ''">
            AND teba2.opFrom &lt; DATEADD(day,1,#{entBase.opTo})
        </if>
        <if test="entBase.marketName != null and entBase.marketName != ''">
            AND teb.entName in (${entBase.marketName})
        </if>
        <if test="entBase.localAdm != null and entBase.localAdm != ''">
            AND teba.localAdm = #{entBase.localAdm}
        </if>
        <if test="entBase.industryPhy != null and entBase.industryPhy != ''">
            AND teb.industryPhy =#{entBase.industryPhy}
        </if>
        <if test="entBase.industryCo != null and entBase.industryCo != ''">
            AND teb.industryCo = #{entBase.industryCo}
        </if>
        <if test="entBase.industryName != null and entBase.industryName != ''">
            AND thi.industryName in (${entBase.industryName})
        </if>
        <if test="entBase.importAreaName != null and entBase.importAreaName != ''">
            AND tia.areaName in (${entBase.importAreaName})
        </if>
        <if test="entBase.iscmBaseInfo != null and entBase.iscmBaseInfo != ''">
            <if test="entBase.checkYear != null and entBase.checkYear != ''">
                <choose>
                    <when test="entBase.iscmBaseInfo == 1 ">
                        AND exists (select 1 from TREntBaseInfo tbi where teb.priPID =tbi.EntityNo and tbi.reportYear =
                        #{entBase.checkYear})
                    </when>
                    <otherwise>
                        AND not exists (select EntityNo from TREntBaseInfo tbi where teb.priPID =tbi.EntityNo and
                        tbi.reportYear =
                        #{entBase.checkYear})
                    </otherwise>
                </choose>
            </if>
            <if test="entBase.checkYear == null and entBase.checkYear ==''">
                <choose>
                    <when test="entBase.iscmBaseInfo == 1 ">
                        AND exists (select 1 from TREntBaseInfo tbi where teb.priPID =tbi.EntityNo and tbi.reportYear =
                        year(getdate())-1)
                    </when>
                    <otherwise>
                        AND not exists (select EntityNo from TREntBaseInfo tbi where teb.priPID =tbi.EntityNo and
                        tbi.reportYear =
                        year(getdate())-1)
                    </otherwise>
                </choose>
            </if>
        </if>
        <if test="entBase.checkYear != null and entBase.checkYear != '' and entBase.iscmBaseInfo == null and entBase.iscmBaseInfo == ''">
            AND exists (select 1 from TREntBaseInfo tbi where teb.priPID =tbi.EntityNo and tbi.reportYear =
            #{entBase.checkYear})
        </if>
        <if test="entBase.hasArea != null and entBase.hasArea != ''">
            <choose>
                <when test="entBase.hasArea == 1 ">
                    and teba.areaID is not null
                </when>
                <otherwise>
                    and teba.areaID is null
                </otherwise>
            </choose>
        </if>
    </select>

    <select id="queryEntBaseInfoByRegno" parameterType="java.lang.String" resultMap="baseMap">
        select * from  TREntbase teb
        where 1=1
        <if test="regNO != null and regNO !=''">
            <choose>
                <when test="regNO.trim().length() == 18">
                    AND teb.USCC=#{regNO}
                </when>
                <otherwise>
                    AND teb.regNO=#{regNO}
                </otherwise>
            </choose>
        </if>
    </select>
</mapper>
