<?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.xinelu.manage.mapper.chatrecord.ChatRecordMapper">

    <resultMap type="ChatRecord" id="ChatRecordResult">
        <result property="id" column="id"/>
	    <result property="messageNo" column="message_no"/>
	    <result property="messageCategory" column="message_category"/>
	    <result property="sourceType" column="source_type"/>
        <result property="consultationId" column="consultation_id"/>
	    <result property="crowds" column="crowds"/>
	    <result property="crowdsName" column="crowds_name"/>
        <result property="senderId" column="sender_id"/>
        <result property="senderName" column="sender_name"/>
        <result property="sendTime" column="send_time"/>
        <result property="recipientId" column="recipient_id"/>
        <result property="recipientName" column="recipient_name"/>
        <result property="messageType" column="message_type"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
        <result property="contentId" column="content_id"/>
        <result property="readStatus" column="read_status"/>
        <result property="readTime" column="read_time"/>
        <result property="delFlag" column="del_flag"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateBy" column="update_by"/>
        <result property="updateTime" column="update_time"/>
    </resultMap>

    <sql id="selectChatRecordVo">
        select id,
               message_no,
               message_category,
               source_type,
               consultation_id,
               crowds,
               crowds_name,
               sender_id,
               sender_name,
               send_time,
               recipient_id,
               recipient_name,
               message_type,
               title,
               content,
               content_id,
               read_status,
               read_time,
               del_flag,
               create_by,
               create_time,
               update_by,
               update_time
        from chat_record
    </sql>

	<sql id="Message_Vo_List">
		message_no
		,message_category,source_type,consultation_id,crowds,crowds_name,sender_id,
        sender_name,send_time,recipient_id,
        recipient_name,message_type,title,content,content_id,
        read_status,title
	</sql>

	<select id="selectMegVoList" parameterType="com.xinelu.manage.dto.chatrecord.MessageSearchDto"
		resultType="com.xinelu.manage.vo.chatrecord.MessageVo">
		select
		<include refid="Message_Vo_List"/>
		from chat_record
		where del_flag = 0
		  and (recipient_id = #{recipientId} or sender_id = #{recipientId}
		<if test="crowdNoList != null and crowdNoList.size() > 0">
			or
			<foreach collection="crowdNoList" item="no" open="(" separator="or" close=")">
				FIND_IN_SET(#{no}, crowds)
			</foreach>
			or crowds = '0'
		</if>
		)
		<if test="messageCategory != null">
			and message_category = #{messageCategory}
		</if>
		<if test="readStatus != null">
			and read_status = #{readStatus}
		</if>
		order by send_time desc
	</select>


    <select id="selectMegList" resultType="com.xinelu.manage.vo.chatrecord.MessageVo">
        select <include refid="Message_Vo_List"/>
        from chat_record
        where del_flag = '0' and message_category = #{messageCategory}
        <!-- 查询居民注册之后的通知消息 -->
          <if test="bindingTime != null">
              and send_time &gt;= #{bindingTime}
          </if>
        <choose>
            <!-- 健康推送 -->
            <when test="messageCategory == 2">
                and recipient_id is null
                <!-- 查询适用人群的通知消息 -->
                <if test="crowdNoList != null and crowdNoList.size() > 0">
                    and (
                    <foreach collection="crowdNoList" item="no" open="(" separator="or" close=")">
                        FIND_IN_SET(#{no},crowds)
                    </foreach>
                    or crowds = '0')
                </if>
                <!-- 查询签约医生的通知 -->
                and sender_id = #{senderId}
            </when>
            <!-- 通知公告 -->
            <when test="messageCategory == 1">
                and recipient_id is null
            </when>
            <!-- 消息通知 -->
            <when test="messageCategory == 4">
                and recipient_id = #{recipientId}
            </when>
        </choose>
        order by send_time desc
    </select>

	<select id="selectOneChatRecord" parameterType="java.lang.Long"
		resultType="com.xinelu.manage.vo.chatrecord.MessageCenterVo">
		select message_category,
		       consultation_id,
		       crowds,
		       crowds_name,
		       sender_id,
		       sender_name,
		       send_time,
		       message_type,
		       content,
		       recipient_id,
		       recipient_name
		from chat_record
		where del_flag = 0
		  and message_category = '3'
		  and consultation_id = #{consultationId}
		ORDER BY send_time desc limit 1
	</select>

    <select id="selectChatRecordList" parameterType="ChatRecord" resultMap="ChatRecordResult">
        <include refid="selectChatRecordVo"/>
        where del_flag = 0
        and consultation_id = #{consultationId}
        and (
        (sender_id = #{senderId} and recipient_id = #{recipientId})
        or
        (sender_id = #{recipientId} and recipient_id = #{senderId})
        )
        <if test="readStatus != null">
            and read_status = #{readStatus}
        </if>
        order by send_time
    </select>

    <select id="selectChatRecordById" parameterType="Long"
            resultMap="ChatRecordResult">
        <include refid="selectChatRecordVo"/>
        where id = #{id}
    </select>

    <!-- 查询健康知识推送列表 -->
    <select id="getNoticList" resultType="com.xinelu.manage.vo.chatrecord.MessageVo">
        <include refid="selectChatRecordVo"></include>
        where del_flag = '0' and sender_id = #{senderId}
        <choose>
            <when test="messageCategory != null and messageCategory != ''">
                and message_category = #{messageCategory}
            </when>
            <otherwise>
                and message_category in ('1', '2')
            </otherwise>
        </choose>
        <if test="startDate != null">
            and send_time &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            and send_time &lt;= #{endDate}
        </if>
        <if test="crowdNoList != null and crowdNoList.size() > 0">
            and (
            <foreach collection="crowdNoList" item="no" open="(" separator="or" close=")">
                FIND_IN_SET(#{no},crowds)
            </foreach>)
        </if>
        order by send_time desc
    </select>

    <!-- 查询消息详情 -->
    <select id="getByNo" resultType="com.xinelu.manage.vo.chatrecord.MessageVo">
        select cr.id,
            cr.message_no,
            cr.message_category,
            cr.consultation_id,
            cr.crowds,
            cr.crowds_name,
            cr.sender_id,
            cr.sender_name,
            cr.send_time,
            cr.recipient_id,
            cr.recipient_name,
            cr.message_type,
            cr.title,
            cr.content,
            cr.content_id,
            cr.read_status,
            cr.read_time,
            cr.del_flag,
            cr.create_by,
            cr.create_time,
            cr.update_by,
            cr.update_time,
            p.person_code sender_no
        from chat_record cr
        left join hospital_person_info p on p.id = cr.sender_id
        where cr.message_no = #{messageNo}
    </select>

    <insert id="insertChatRecord" parameterType="ChatRecord" useGeneratedKeys="true"
            keyProperty="id">
        insert into chat_record
        <trim prefix="(" suffix=")" suffixOverrides=",">
	        <if test="messageCategory != null">message_category,
	        </if>
	        <if test="sourceType != null">source_type,
	        </if>
            <if test="messageNo != null">message_no,
            </if>
            <if test="consultationId != null">consultation_id,
            </if>
	        <if test="crowds != null">crowds,
	        </if>
	        <if test="crowdsName != null">crowds_name,
	        </if>
            <if test="senderId != null">sender_id,
            </if>
            <if test="senderName != null and senderName != ''">sender_name,
            </if>
            <if test="sendTime != null">send_time,
            </if>
            <if test="recipientId != null">recipient_id,
            </if>
            <if test="recipientName != null and recipientName != ''">recipient_name,
            </if>
            <if test="messageType != null">message_type,
            </if>
            <if test="title != null">title,
            </if>
            <if test="content != null">content,
            </if>
            <if test="contentId != null">content_id,
            </if>
            <if test="readStatus != null">read_status,
            </if>
            <if test="readTime != null">read_time,
            </if>
            <if test="delFlag != null">del_flag,
            </if>
            <if test="createBy != null">create_by,
            </if>
            <if test="createTime != null">create_time,
            </if>
            <if test="updateBy != null">update_by,
            </if>
            <if test="updateTime != null">update_time,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
	        <if test="messageCategory != null">#{messageCategory},
	        </if>
	        <if test="sourceType != null">#{sourceType},
	        </if>
            <if test="messageNo != null">#{messageNo},
            </if>
            <if test="consultationId != null">#{consultationId},
            </if>
	        <if test="crowds != null">#{crowds},
	        </if>
	        <if test="crowdsName != null">#{crowdsName},
	        </if>
            <if test="senderId != null">#{senderId},
            </if>
            <if test="senderName != null and senderName != ''">#{senderName},
            </if>
            <if test="sendTime != null">#{sendTime},
            </if>
            <if test="recipientId != null">#{recipientId},
            </if>
            <if test="recipientName != null and recipientName != ''">#{recipientName},
            </if>
            <if test="messageType != null">#{messageType},
            </if>
            <if test="title != null">#{title},
            </if>
            <if test="content != null">#{content},
            </if>
            <if test="contentId != null">#{contentId},
            </if>
            <if test="readStatus != null">#{readStatus},
            </if>
            <if test="readTime != null">#{readTime},
            </if>
            <if test="delFlag != null">#{delFlag},
            </if>
            <if test="createBy != null">#{createBy},
            </if>
            <if test="createTime != null">#{createTime},
            </if>
            <if test="updateBy != null">#{updateBy},
            </if>
            <if test="updateTime != null">#{updateTime},
            </if>
        </trim>
    </insert>

    <update id="updateChatRecord" parameterType="ChatRecord">
        update chat_record set
	    read_status =  #{readStatus},
	    read_time = #{readTime}
	    where del_flag = '0'
	        <if test="messageCategory != null">
	        	and message_category = #{messageCategory}
	        </if>
            <if test="consultationId != null">
	    		and consultation_id = #{consultationId}
            </if>
	        <if test="crowds != null">
	        	and crowds = #{crowds}
	        </if>
	        <if test="crowdsName != null">
	        	and crowds_name = #{crowdsName}
	        </if>
            <if test="senderId != null">
            	and sender_id = #{senderId}
            </if>
            <if test="senderName != null and senderName != ''">
            	and sender_name = #{senderName}
            </if>
            <if test="sendTime != null">
                and send_time = #{sendTime}
            </if>
            <if test="recipientId != null">
                and recipient_id = #{recipientId}
            </if>
            <if test="messageType != null">
            	and message_type = #{messageType}
            </if>
	        <if test="id != null">
	        	and id = #{id}
	        </if>
    </update>

    <update id="updateReadStatus" parameterType="ChatRecord">
        update chat_record
        <trim prefix="SET" suffixOverrides=",">
            <if test="readStatus != null and readStatus != ''">
                read_status = #{readStatus},
            </if>
            <if test="readTime != null">
                read_time = #{readTime},
            </if>
        </trim>
        where del_flag = 0 and read_status = '0' and consultation_id = #{consultationId} and recipient_id = #{recipientId}
    </update>

    <!-- 更新通知 -->
    <update id="updateChatRecordOfNo">
        update chat_record
        <set>
            <if test="messageCategory != null">message_category = #{messageCategory},</if>
	        <if test="sourceType != null">source_type = #{sourceType},</if>
            <if test="consultationId != null">consultation_id = #{consultationId},</if>
            <if test="crowds != null">crowds = #{crowds},</if>
            <if test="crowdsName != null">crowds_name = #{crowdsName},</if>
            <if test="senderId != null">sender_id = #{senderId},</if>
            <if test="senderName != null and senderName != ''">sender_name = #{senderName},</if>
            <if test="sendTime != null">send_time = #{sendTime},</if>
            <if test="recipientId != null">recipient_id = #{recipientId},</if>
            <if test="recipientName != null and recipientName != ''">recipient_name = #{recipientName},</if>
            <if test="messageType != null">message_type = #{messageType},</if>
            <if test="title != null">title = #{title},</if>
            <if test="content != null">content = #{content},</if>
            <if test="contentId != null">content_id = #{contentId},</if>
            <if test="readStatus != null">read_status = #{readStatus},</if>
            <if test="readTime != null">read_time = #{readTime},</if>
            <if test="delFlag != null">del_flag =#{delFlag},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
        </set>
        where message_no = #{messageNo}
    </update>

    <delete id="deleteChatRecordById" parameterType="Long">
        delete
        from chat_record
        where id = #{id}
    </delete>

    <delete id="deleteChatRecordByIds" parameterType="String">
        delete from chat_record where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

    <!-- 删除通知 -->
    <delete id="del">
        delete from chat_record where message_no = #{messageNo}
    </delete>

	<update id="deleteMegs">
		update chat_record set del_flag = 1
		<where>
			<if test="ids != null">
				id in
				<foreach collection="ids" item="id" open="(" separator="," close=")">
					#{id}
				</foreach>
			</if>
		</where>
	</update>

	<select id="getList"
		resultType="com.xinelu.manage.domain.chatRecord.ChatRecord">
		select id,
		       message_category,
		       consultation_id,
		       crowds,
		       crowds_name,
		       sender_id,
		       sender_name,
		       send_time,
		       message_type,
		       content,
		       recipient_id,
		       recipient_name
		from chat_record
		where del_flag = 0
		<if test="sendTime != null">
		    and send_time &lt;= #{sendTime}
        </if>
		<if test="messageCategory != null">
		    and message_category = #{messageCategory}
		</if>
		ORDER BY send_time desc
	</select>
</mapper>
