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

    <select id="query" resultType="com.qmth.sop.business.bean.result.SysMessageResult">
        SELECT
        m.receive_time,
        ua.CODE,
        u.real_name NAME,
        ua.province,
        ua.city,
        u.mobile_number,
        s.NAME supplier,
        bs.NAME service,
        c.NAME custom,
        c.type,
        m.status
        FROM
        sys_message m
        LEFT JOIN sys_user u ON u.id = m.receive_user_id
        left join t_b_crm crm on crm.crm_no=m.crm_no
        LEFT JOIN sys_custom c ON c.id = crm.custom_id
        LEFT JOIN t_b_user_archives ua ON u.mobile_number = ua.mobile_number
        LEFT JOIN sys_supplier s ON s.id = m.supplier_id
        LEFT JOIN t_b_service bs ON bs.id = m.service_id
        <where>
            <if test="noticeId != null and noticeId != ''">
                and m.notice_id = #{noticeId}
            </if>
            <if test="city != null and city != ''">
                and ua.city = #{city}
            </if>
            <if test="supplierId != null and supplierId != ''">
                and m.supplier_id = #{supplierId}
            </if>
<!--            <if test="status != null and status != ''">-->
<!--                and m.status = #{status}-->
<!--            </if>-->
            <if test="status != null">
                <choose>
                    <when test="status">
                        and m.status = 1
                    </when>
                    <otherwise>
                        and m.status = 0
                    </otherwise>
                </choose>
            </if>
        </where>
        order by m.send_time desc
    </select>

    <select id="count" resultType="com.qmth.sop.business.bean.result.SysMessageCountResult">
        SELECT count(a.id) total,sum(a.`status`) reviewed FROM sys_message a where a.notice_id=#{noticeId}
    </select>


    <select id="countByTypes" resultType="java.lang.Long">
        SELECT count(a.id) FROM sys_message a where a.receive_user_id=#{userId} and a.status=0
        <if test="typeStr != null and typeStr != ''">
            and a.type in
            <foreach collection="typeStr" item="type" open="(" separator="," close=")">
                #{type}
            </foreach>
        </if>
    </select>

    <select id="pageByTypes" resultType="com.qmth.sop.business.bean.result.SysMessageWorkResult">
        SELECT distinct
        m.id,
        m.code,
        m.type message_type,
        m.receive_time,
        m.send_time,
        m.STATUS read_status,
        m.content,
        m.obj_type,
        u.real_name form_user,
        c.NAME custom ,
        c.type custom_type ,
        bs.name service,
        n.title
        FROM
        sys_message m
        LEFT JOIN sys_notice n ON n.id = m.notice_id
        LEFT JOIN sys_user u ON u.id = m.form_user_id
        LEFT JOIN t_b_service bs ON bs.id = m.service_id
        left join t_b_crm crm on crm.crm_no=m.crm_no
        LEFT JOIN sys_custom c ON c.id = crm.custom_id
        <where>
            <if test="userId != null and userId != ''">
                and m.receive_user_id = #{userId}
            </if>
            <if test="typeStr != null and typeStr != ''">
                and m.type in
                <foreach collection="typeStr" item="type" open="(" separator="," close=")">
                    #{type}
                </foreach>
            </if>
            <if test="custom != null and custom != ''">
                and c.name like concat('%', #{custom} , '%')
            </if>
            <if test="status != null">
                <choose>
                    <when test="status">
                        and m.status = 1
                    </when>
                    <otherwise>
                        and m.status = 0
                    </otherwise>
                </choose>
            </if>
            <if test="serviceId != null and serviceId != ''">
                and m.service_id = #{serviceId}
            </if>
<!--            <if test="query != null and query != ''">-->
<!--                and m.content like concat('%', #{query} , '%')-->
<!--            </if>-->
            <if test="query != null and query != ''">
                and n.title like concat('%', #{query} , '%')
            </if>
            <if test="startTime != null and startTime != '' ">
                <![CDATA[
               and m.send_time >= #{startTime}
               ]]>
            </if>
            <if test="endTime != null and endTime != ''">
                <![CDATA[
                and m.send_time <= #{endTime}
                ]]>
            </if>
        </where>
        order by m.send_time desc
    </select>

</mapper>