<?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.qmth.sop.business.mapper.SysNoticeMapper">

    <select id="query" resultType="com.qmth.sop.business.bean.result.SysNoticeResult">
        SELECT
        a.*,
        su.real_name AS createName,
        m.readCount
        FROM
        sys_notice a
        LEFT JOIN sys_user su ON su.id = a.create_id
        LEFT JOIN ( SELECT m.notice_id, CONCAT( sum( m.STATUS ), '/', count( m.id )) readCount FROM sys_message m GROUP BY m.notice_id ) m ON m.notice_id = a.id
        <where>
            and a.enable = 1
            <if test="query != null and query != ''">
                and a.content like concat('%', #{query} , '%')
            </if>
            <if test="status != null and status != ''">
                and a.status = #{status}
            </if>
            <if test="type != null and type != ''">
                and a.type = #{type}
            </if>
            <if test="startTime != null and startTime != '' ">
                <![CDATA[
               and a.publish_time >= #{startTime}
               ]]>
            </if>
            <if test="endTime != null and endTime != ''">
                <![CDATA[
                and a.publish_time <= #{endTime}
                ]]>
            </if>
        </where>
        order by a.publish_time desc
    </select>
</mapper>