<?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.forcedCancellation.mapper.LicenseCancellationDeclareMapper">

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        RECID, BIZSEQID, PRIPID, ENTNAME, UNISCID, REGORG, REGORG_CN, REGTYPE, LICCANREASON, ISORICOP, LICCANSTA, LICCANDATE, LICCOPYNO, S_EXT_FROMNODE, S_EXT_DATATIME,CREATE_TIME,LAUPTIME
    </sql>

    <!-- 分页查询营业执照作废声明 -->
    <select id="selectByPage" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        <where>
            <if test="params.bizseqId != null and params.bizseqId != ''">
                AND BIZSEQID = #{params.bizseqId}
            </if>
            <if test="params.pripid != null and params.pripid != ''">
                AND PRIPID = #{params.pripid}
            </if>
            <if test="params.entName != null and params.entName != ''">
                AND ENTNAME LIKE CONCAT('%', #{params.entName}, '%')
            </if>
            <if test="params.uniscid != null and params.uniscid != ''">
                AND UNISCID = #{params.uniscid}
            </if>
            <if test="params.regOrg != null and params.regOrg != ''">
                AND REGORG = #{params.regOrg}
            </if>
            <if test="params.regType != null and params.regType != ''">
                AND REGTYPE = #{params.regType}
            </if>
            <if test="params.licCanReason != null and params.licCanReason != ''">
                AND LICCANREASON = #{params.licCanReason}
            </if>
            <if test="params.isoriCop != null and params.isoriCop != ''">
                AND ISORICOP = #{params.isoriCop}
            </if>
            <if test="params.licCanDate != null">
                AND LICCANDATE = #{params.licCanDate}
            </if>
            <if test="params.licCopyNo != null and params.licCopyNo != ''">
                AND LICCOPYNO LIKE CONCAT('%', #{params.licCopyNo}, '%')
            </if>
            <if test="params.sExtFromnode != null and params.sExtFromnode != ''">
                AND S_EXT_FROMNODE = #{params.sExtFromnode}
            </if>
        </where>
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据业务ID查询营业执照作废声明 -->
    <select id="selectByBizSeqId" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE BIZSEQID = #{bizseqId}
        ORDER BY S_EXT_DATATIME DESC
        LIMIT 1
    </select>

    <!-- 根据主体标识查询营业执照作废声明 -->
    <select id="selectByPripid" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE PRIPID = #{pripid}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据企业名称查询营业执照作废声明 -->
    <select id="selectByEntName" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE ENTNAME LIKE CONCAT('%', #{entName}, '%')
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据统一社会信用代码查询营业执照作废声明 -->
    <select id="selectByUniscid" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE UNISCID = #{uniscid}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据登记机关查询营业执照作废声明 -->
    <select id="selectByRegOrg" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE REGORG = #{regOrg}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据营业执照作废原因查询 -->
    <select id="selectByLicCanReason" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE LICCANREASON = #{licCanReason}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据正副本标识查询 -->
    <select id="selectByIsoriCop" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE ISORICOP = #{isoriCop}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据作废声明发布日期查询 -->
    <select id="selectByLicCanDate" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE LICCANDATE = #{licCanDate}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据营业执照副本编号查询 -->
    <select id="selectByLicCopyNo" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE LICCOPYNO LIKE CONCAT('%', #{licCopyNo}, '%')
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据二级部署标识查询 -->
    <select id="selectBySExtFromnode" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE S_EXT_FROMNODE = #{sExtFromnode}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据业务ID和主体标识查询最新的作废声明 -->
    <select id="selectLatestByBizAndPripid" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE BIZSEQID = #{bizseqId}
        AND PRIPID = #{pripid}
        ORDER BY S_EXT_DATATIME DESC
        LIMIT 1
    </select>

    <!-- 根据登记类型和作废原因统计 -->
    <select id="countByRegTypeAndReason" resultType="int">
        SELECT COUNT(1)
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE REGTYPE = #{regType}
        AND LICCANREASON = #{licCanReason}
    </select>

    <!-- 根据作废日期范围查询 -->
    <select id="selectByLicCanDateRange" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE LICCANDATE >= #{startDate}
        AND LICCANDATE &lt;= #{endDate}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据登记机关和作废日期范围查询 -->
    <select id="selectByRegOrgAndDateRange" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE REGORG = #{regOrg}
        AND LICCANDATE >= #{startDate}
        AND LICCANDATE &lt;= #{endDate}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 批量更新作废声明发布日期 -->
    <update id="batchUpdateLicCanDate">
        UPDATE AICCS.LICENSE_CANCELLATION_DECLARE
        SET LICCANDATE = #{licCanDate},
            S_EXT_DATATIME = SYSTIMESTAMP
        WHERE RECID IN
        <foreach collection="recIds" item="recId" open="(" separator="," close=")">
            #{recId}
        </foreach>
    </update>

    <!-- 根据二级部署标识和日期范围查询 -->
    <select id="selectByNodeAndDateRange" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE S_EXT_FROMNODE = #{sExtFromnode}
        AND LICCANDATE >= #{startDate}
        AND LICCANDATE &lt;= #{endDate}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据正副本标识和作废原因统计 -->
    <select id="countByIsoriCopAndReason" resultType="int">
        SELECT COUNT(1)
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE ISORICOP = #{isoriCop}
        AND LICCANREASON = #{licCanReason}
    </select>

    <!-- 根据主体标识和日期范围查询 -->
    <select id="selectByPripidAndDateRange" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.LicenseCancellationDeclare">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.LICENSE_CANCELLATION_DECLARE
        WHERE PRIPID = #{pripid}
        AND LICCANDATE >= #{startDate}
        AND LICCANDATE &lt;= #{endDate}
        ORDER BY S_EXT_DATATIME DESC
    </select>

</mapper>
