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

    <select id="FlowMsgReport" resultType="com.qmth.sop.business.bean.report.FlowMsgReferReportBean">
        select t.*,
               ROUND(t.readMsg / sum,2) * 100 as readRate,
               ROUND(t.unReadMsg / sum,2) * 100 as unReadRate from(select
               distinct su.real_name as name,
                        count(sm.id) as sum,
                        sum(IF(sm.status = 1,1,0)) as readMsg,
                        sum(IF(sm.status = 0,1,0)) as unReadMsg
           from
               sys_message sm
               join t_b_service tbs on tbs.id = sm.service_id
               join sys_user su on su.id = sm.receive_user_id
           where sm.service_id = #{serviceId}
             and tbs.status <![CDATA[ <> ]]> 'CANCEL'
             and sm.type = #{messageTypeEnum}
           group by su.real_name) t
        order by sum desc
    </select>
</mapper>
