<?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.abnormal.mapper.ToincludeabnMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.chinaweal.aiccs.aiccs.abnormal.entity.Toincludeabn">
        <id column="toIncludeId" property="toIncludeId"/>
        <result column="pripid" property="pripid"/>
        <result column="regno" property="regno"/>
        <result column="uniscid" property="uniscid"/>
        <result column="includeType" property="includeType"/>
        <result column="state" property="state"/>
        <result column="reportYear" property="reportYear"/>
        <result column="updateTime" property="updateTime"/>
        <result column="insertTime" property="insertTime"/>
        <result column="dataState" property="dataState"/>
        <result column="dataFrom" property="dataFrom"/>
        <result column="dataFromId" property="dataFromId"/>
        <result column="entOrgDer" property="entOrgDer"/>
        <result column="updateUserId" property="updateUserId"/>
        <result column="inspectobjid" property="inspectobjid"/>
    </resultMap>


    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        toIncludeId
        , pripid, regno, uniscid, includeType, state, reportYear, updateTime, insertTime, dataState,dataFrom,dataFromId,
        entOrgDer,updateUserId,inspectobjid
    </sql>

    <select id="selectToincludeabnList" resultMap="BaseResultMap">
        SELECT *
        from toincludeabn
        where dataState = '0'
          and (includeType = '3' or includeType = '4')
    </select>

    <select id="selectReportYears" resultMap="BaseResultMap">
        SELECT t.pripid, GROUP_CONCAT(t.REPORTYEAR ORDER BY t.REPORTYEAR desc) as reportYear
        from toincludeabn t
        <where>
            dataState = '0' and includeType = '1' and state = '0'
            <if test="pripid != null and pripid neq ''">
                and pripid = #{pripid}
            </if>
            group by t.PRIPID
        </where>
    </select>

    <select id="listByOrgCount" resultType="java.lang.Long">
        SELECT count(1)
        FROM toincludeabn t
        LEFT JOIN e_baseinfo e on t.pripid = e.pripid
        LEFT JOIN cxaicorg.t_orgunits g on e.SUPORG = g.orgnumber
        <include refid="listByOrgWhere"/>
    </select>

    <select id="listByOrg" resultType="com.chinaweal.aiccs.aiccs.abnormal.entity.dto.ToIncludeAbnDto">
        SELECT
        <!--        e.*,-->
        e.PRIPID, e.entname, e.uniscid, e.regno,e.enttype, e.enttype_cn, e.regorg,e.regorg_cn,e.name,e.dom,
        t.toIncludeId,t.regEntryUserName,t.regEntryUserId,t.reportYear,t.includeType,
        e.SUPORG as annCheDept,g.orgunitname as annCheDeptCn, t.dataFrom,t.dataState, t.isbatch
        FROM toincludeabn t
        LEFT JOIN e_baseinfo e on t.pripid = e.pripid
        LEFT JOIN cxaicorg.t_orgunits g on e.SUPORG = g.orgnumber
        <include refid="listByOrgWhere"/>
        limit ${(curPage - 1)*pageSize}, ${pageSize}
    </select>

    <sql id="listByOrgWhere">
        <where>
            t.state = '0' and t.dataState = '0' and t.includeType in ('2','3','4')
            AND (
            t.includeType != '4'
            OR NOT EXISTS (
            SELECT 1 FROM tsabnlist a
            WHERE a.pripid = e.pripid
            AND a.OPERATESTATUS = '1'
            AND t.insertTime <![CDATA[<]]> '2023-01-17 00:00:00'
            AND (a.SPEREASONS LIKE '%住所%' OR a.SPEREASONS LIKE '%联系%')
            )
            )
            <if test="params.uniscid != null and params.uniscid !=''">
                and e.UNISCID = #{params.uniscid}
            </if>
            <!--                <if test="params.tbm != null and params.tbm !=''">-->
            <!--                    and t.dataFrom != '3' and t.dataFrom != '4'-->
            <!--                </if>-->
            <if test="params.regno != null and params.regno !=''">
                and e.regno = #{params.regno}
            </if>
            <if test='params.entertype =="individual"'>
                and e.enttype = '9910'
            </if>
            <if test='params.entertype =="company"'>
                and e.enttype not in ('9910','9100','9200')
            </if>
            <if test='params.entertype =="farmer"'>
                and e.enttype in ('9100','9200')
            </if>
            <if test="params.searchName != null and params.searchName !=''">
                and e.ENTNAME like concat('%', #{params.searchName}, '%')
            </if>
            <if test="params.regOrg != null and params.regOrg !='' ">
                and e.REGORG like concat(#{params.regOrg}, '%')
            </if>
            <if test="params.acceptOrg != null and params.acceptOrg == '440129' ">
                and (e.ACCEPTREGORG = '440129' or e.REGORG = '440129')
            </if>
            <if test="params.acceptOrg != null and params.acceptOrg == '440101' ">
                and e.ACCEPTREGORG != '440129'
            </if>
            <if test="params.scountrycodes != null and params.scountrycodes!='' ">
                and e.SUPORG = #{params.scountrycodes}
            </if>
            <!--            <if test="params.scountrycodes != null and params.scountrycodes.size() > 0">-->
            <!--                and e.SUPORG in-->
            <!--                <foreach collection="params.scountrycodes" item="AnnCheDept" open="(" separator="," close=")">-->
            <!--                    #{AnnCheDept}-->
            <!--                </foreach>-->
            <!--            </if>-->
            <!--            <choose>-->
            <!--                <when test="params.regOrg != null and params.regOrg !='' ">-->
            <!--                    and e.REGORG = #{params.regOrg}-->
            <!--                </when>-->
            <!--                <when test="params.acceptOrg != null and params.acceptOrg !='' ">-->
            <!--                    and e.ACCEPTREGORG = #{params.acceptOrg}-->
            <!--                </when>-->
            <!--                <when test="params.scountrycodes != null and params.scountrycodes.size() > 0">-->
            <!--                    and e.SUPORG in-->
            <!--                    <foreach collection="params.scountrycodes" item="AnnCheDept" open="(" separator="," close=")">-->
            <!--                        #{AnnCheDept}-->
            <!--                    </foreach>-->
            <!--                </when>-->
            <!--                <otherwise>-->
            <!--                    and e.SUPORG = '-1'-->
            <!--                </otherwise>-->
            <!--            </choose>-->
            <if test="params.regulatory != null and params.regulatory !=''">
                and t.regulatory = #{params.regulatory}
            </if>
            <if test="params.regEntryUserName != null and params.regEntryUserName !=''">
                and t.regEntryUserName like concat('%', #{params.regEntryUserName}, '%')
            </if>
            <if test="params.includeType != null and params.includeType !=''">
                and t.includeType = #{params.includeType}
            </if>
            <choose>
                <when test="params.addressSearchType != null and params.addressSearchType !=''">
                    <if test="params.addressSearchType eq 'eq'">
                        and
                        (
                        e.oploc = #{params.searchDom}
                        or
                        e.dom = #{params.searchDom}
                        )
                    </if>
                    <if test="params.addressSearchType eq 'like'">
                        and
                        (
                        e.oploc like concat('%', #{params.searchDom}, '%')
                        or
                        e.dom like concat('%', #{params.searchDom}, '%')
                        )
                    </if>
                </when>
                <otherwise>
                    <if test="params.searchDom != null and params.searchDom !=''">
                        and
                        (
                        e.oploc like concat('%', #{params.searchDom}, '%')
                        or
                        e.dom like concat('%', #{params.searchDom}, '%')
                        )
                    </if>
                </otherwise>
            </choose>
        </where>
    </sql>

    <select id="listByEb" resultType="com.chinaweal.aiccs.aiccs.abnormal.entity.dto.ToIncludeAbnDto">
        select * from aiccs.e_baseinfo e where 1=1
        <if test="params.uniscid != null and params.uniscid !=''">
            and e.UNISCID = #{params.uniscid}
        </if>
        <if test="params.regno != null and params.regno !=''">
            and e.regno = #{params.regno}
        </if>
        <if test="params.searchName != null and params.searchName !=''">
            and e.ENTNAME like concat('%', #{params.searchName}, '%')
        </if>
        <if test="params.suporg != null and params.suporg !=''">
            and (e.suporg like concat(#{params.suporg}, '%') or e.regorg like concat(#{params.suporg}, '%'))
        </if>
    </select>
</mapper>
