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

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        NOTICEID, BIZSEQID, NOTICETITLE, NOTICECONTENT, JUDAUTH, JUDAUTH_CN, ANNDATE, NOTICEFROM, NOTICETO, WEBSITE, S_EXT_FROMNODE, S_EXT_DATATIME,CREATE_TIME,LAUPTIME
    </sql>

    <!-- 分页查询强制注销登记决定书送达公告 -->
    <select id="selectByPage" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        <where>
            <if test="params.bizseqId != null and params.bizseqId != ''">
                AND BIZSEQID = #{params.bizseqId}
            </if>
            <if test="params.noticeTitle != null and params.noticeTitle != ''">
                AND NOTICETITLE LIKE CONCAT('%', #{params.noticeTitle}, '%')
            </if>
            <if test="params.judAuth != null and params.judAuth != ''">
                AND JUDAUTH = #{params.judAuth}
            </if>
            <if test="params.annDate != null">
                AND ANNDATE >= #{params.annDate}
            </if>
            <if test="params.annDateEnd != null">
                AND ANNDATE &lt;= #{params.annDateEnd}
            </if>
            <if test="params.noticeFrom != null">
                AND NOTICEFROM >= #{params.noticeFrom}
            </if>
            <if test="params.noticeTo != null">
                AND NOTICETO &lt;= #{params.noticeTo}
            </if>
            <if test="params.sExtFromnode != null and params.sExtFromnode != ''">
                AND S_EXT_FROMNODE = #{params.sExtFromnode}
            </if>
            <if test="params.website != null and params.website != ''">
                AND WEBSITE LIKE CONCAT('%', #{params.website}, '%')
            </if>
        </where>
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据业务ID查询强制注销登记决定书送达公告 -->
    <select id="selectByBizSeqId" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        WHERE BIZSEQID = #{bizseqId}
        ORDER BY S_EXT_DATATIME DESC
        LIMIT 1
    </select>

    <!-- 根据公告标题查询 -->
    <select id="selectByNoticeTitle" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        WHERE NOTICETITLE LIKE CONCAT('%', #{noticeTitle}, '%')
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据作出决定机关查询 -->
    <select id="selectByJudAuth" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        WHERE JUDAUTH = #{judAuth}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据公告发布日期范围查询 -->
    <select id="selectByAnnDateRange" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        WHERE ANNDATE >= #{annDateFrom}
        AND ANNDATE &lt;= #{annDateTo}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据公告有效日期范围查询 -->
    <select id="selectByNoticeDateRange" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        WHERE NOTICEFROM >= #{noticeFrom}
        AND NOTICETO &lt;= #{noticeTo}
        ORDER BY S_EXT_DATATIME DESC
    </select>

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

    <!-- 根据下载网址查询 -->
    <select id="selectByWebsite" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        WHERE WEBSITE LIKE CONCAT('%', #{website}, '%')
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 更新公告发布日期 -->
    <update id="updateAnnDate">
        UPDATE AICCS.FORCE_LOGOUT_SERV_NOTICE
        SET ANNDATE = #{annDate},
            S_EXT_DATATIME = SYSTIMESTAMP
        WHERE NOTICEID = #{noticeId}
    </update>

    <!-- 更新公告有效期 -->
    <update id="updateNoticePeriod">
        UPDATE AICCS.FORCE_LOGOUT_SERV_NOTICE
        SET NOTICEFROM = #{noticeFrom},
            NOTICETO = #{noticeTo},
            S_EXT_DATATIME = SYSTIMESTAMP
        WHERE NOTICEID = #{noticeId}
    </update>

    <!-- 根据作出决定机关和日期范围查询 -->
    <select id="selectByJudAuthAndDateRange" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        WHERE JUDAUTH = #{judAuth}
        AND ANNDATE >= #{startDate}
        AND ANNDATE &lt;= #{endDate}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 获取当前有效的公告 -->
    <select id="selectValidNotices" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        WHERE ANNDATE &lt;= #{currentDate}
        AND NOTICETO >= #{currentDate}
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 根据公告有效日期范围和二级部署标识查询 -->
    <select id="selectByNoticeDateRangeAndNode" resultType="com.chinaweal.aiccs.aiccs.forcedCancellation.entity.ForceLogoutServNotice">
        SELECT
        <include refid="Base_Column_List"/>
        FROM AICCS.FORCE_LOGOUT_SERV_NOTICE
        WHERE NOTICEFROM >= #{noticeFrom}
        AND NOTICETO &lt;= #{noticeTo}
        <if test="sExtFromnode != null and sExtFromnode != ''">
            AND S_EXT_FROMNODE = #{sExtFromnode}
        </if>
        ORDER BY S_EXT_DATATIME DESC
    </select>

    <!-- 批量更新公告内容 -->
    <update id="batchUpdateNoticeContent">
        UPDATE AICCS.FORCE_LOGOUT_SERV_NOTICE
        SET NOTICETITLE = #{noticeTitle},
            NOTICECONTENT = #{noticeContent},
            S_EXT_DATATIME = SYSTIMESTAMP
        WHERE NOTICEID IN
        <foreach collection="noticeIds" item="noticeId" open="(" separator="," close=")">
            #{noticeId}
        </foreach>
    </update>

</mapper>
