<?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.pavis.admin.system.mapper.NoticeMapper">

    <select id="selectDashboardList"
            resultType="com.pavis.admin.system.model.resp.dashboard.DashboardNoticeResp">
        SELECT
            id, title, type
        FROM sys_notice
        WHERE (effective_time IS NULL OR NOW() > effective_time)
        AND (terminate_time IS NULL OR terminate_time > NOW())
        <if test="userId != null">
            AND (notice_scope = 1 OR (notice_scope = 2 AND JSON_CONTAINS(notice_users, CONCAT('"', #{userId}, '"'))))
        </if>
        ORDER BY sort ASC, effective_time DESC
        LIMIT 5
    </select>

    <select id="selectNoticePage" resultType="com.pavis.admin.system.model.resp.NoticeDetailResp">
        SELECT *
        FROM sys_notice AS t1
        <where>
            <if test="query.userId != null">
                t1.notice_scope = 1 OR (t1.notice_scope = 2 AND JSON_EXTRACT(t1.notice_users, "$[0]") = CAST(#{query.userId} AS CHAR))
                AND (t1.effective_time IS NULL OR NOW() > t1.effective_time)
                AND (t1.terminate_time IS NULL OR t1.terminate_time > NOW())
            </if>
            <if test="query.title != null and query.title != ''">
                AND t1.title LIKE CONCAT('%', #{query.title}, '%')
            </if>
            <if test="query.type != null and query.type != ''">
                AND t1.type = #{query.type}
            </if>
        </where>
        <if test="query.userId != null">
            ORDER BY t1.sort ASC, t1.effective_time DESC
        </if>
        <if test="query.userId == null">
            ORDER BY t1.create_time DESC
        </if>
    </select>
</mapper>