<?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.lframework.xingyun.template.inner.mappers.system.SysNoticeMapper">

    <resultMap id="SysNotice" type="com.lframework.xingyun.template.inner.entity.SysNotice">
        <id column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="content" property="content"/>
        <result column="available" property="available"/>
        <result column="published" property="published"/>
        <result column="create_by" property="createBy"/>
        <result column="create_time" property="createTime"/>
        <result column="update_by" property="updateBy"/>
        <result column="update_time" property="updateTime"/>
        <result column="readed_num" property="readedNum"/>
        <result column="un_read_num" property="unReadNum"/>
        <result column="publish_time" property="publishTime"/>
    </resultMap>

    <resultMap id="QuerySysNoticeByUserDto" type="com.lframework.xingyun.template.inner.dto.system.notice.QuerySysNoticeByUserDto">
        <id column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="readed" property="readed"/>
        <result column="publish_time" property="publishTime"/>
    </resultMap>

    <sql id="SysNotice_sql">
        SELECT
            tb.id,
            tb.title,
            tb.content,
            tb.available,
            tb.published,
            tb.publish_time,
            tb.create_by,
            tb.create_time,
            tb.update_by,
            tb.update_time,
            tb.readed_num,
            tb.un_read_num
        FROM sys_notice AS tb
    </sql>

    <sql id="SysNoticeByUserDto_sql">
        SELECT
            tb.id,
            tb.title,
            tb.publish_time,
            l.readed
        FROM sys_notice_log AS l
        INNER JOIN sys_notice AS tb ON tb.id = l.notice_id
    </sql>

    <select id="query" resultMap="SysNotice">
        <include refid="SysNotice_sql"/>
        <where>
            <if test="vo.title != null and vo.title != ''">
             AND tb.title LIKE CONCAT('%', #{vo.title}, '%')
            </if>
            <if test="vo.createTimeStart != null">
            AND tb.create_time >= #{vo.createTimeStart}
            </if>
            <if test="vo.createTimeEnd != null">
            <![CDATA[
            AND tb.create_time <= #{vo.createTimeEnd}
            ]]>
            </if>
            <if test="vo.available != null">
            AND tb.available = #{vo.available}
            </if>
        </where>
        ORDER BY tb.create_time DESC
    </select>
    <update id="setReaded">
        UPDATE sys_notice SET readed_num = readed_num + 1, un_read_num = un_read_num - 1 WHERE id = #{id}
    </update>
    <select id="queryByUser" resultMap="QuerySysNoticeByUserDto">
        <include refid="SysNoticeByUserDto_sql"/>
        <where>
            <if test="vo.userId != null and vo.userId != ''">
             AND l.user_id = #{vo.userId}
            </if>
            <if test="vo.title != null and vo.title != ''">
             AND tb.title LIKE CONCAT('%', #{vo.title}, '%')
            </if>
            <if test="vo.createTimeStart != null">
            AND tb.publish_time >= #{vo.createTimeStart}
            </if>
            <if test="vo.createTimeEnd != null">
            <![CDATA[
            AND tb.publish_time <= #{vo.createTimeEnd}
            ]]>
            </if>
            <if test="vo.readed != null">
            AND l.readed = #{vo.readed}
            </if>
            AND tb.available = TRUE
        </where>
        ORDER BY tb.update_time DESC
    </select>
</mapper>
