ServiceReplyDao.xml

<?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.casking.ccss.modules.cs.dao.ServiceReplyDao">
<insert id="save">
insert into c_service_reply(
C_S_ID,
C_P_I_ID,
C_S_USER_ID,
C_S_MESSAGE,
C_S_TIME
)
values(
sys_guid(),
#{patientId},
#{userId},
#{message},
#{time}
)
</insert>

<!-- 客服回访信息查询 -->
<select id="findReply" parameterType="com.casking.ccss.modules.cs.entity.ServiceReply"
                   resultType="com.casking.ccss.modules.cs.entity.ServiceReply">
       select 
       r.c_p_i_id as "patientId",
       r.C_S_USER_ID as "userId",
       r.C_S_MESSAGE as "message",
       r.C_S_TIME as "time" 
       from 
       c_service_reply r 
       where r.c_p_i_id = #{id} 
       order by r.C_S_TIME desc

</select>


<!-- 查询回访操作状态 -->
<select id="findReplyStatus" parameterType="com.casking.ccss.modules.cs.entity.ServiceReply"
                   resultType="com.casking.ccss.modules.cs.entity.ServiceReply">
   select s.*
      from (select r.c_s_time as "time",
                   row_number() over(partition by r.C_P_I_ID order by r.C_S_TIME desc) as group_id
             from c_service_reply r
             where r.C_P_I_ID = #{id})s
     where s.group_id = 1
</select>

<!-- 查询回访客服、时间 -->
<select id="findUserId" parameterType="com.casking.ccss.modules.cs.entity.ServiceReply"
                   resultType="com.casking.ccss.modules.cs.entity.ServiceReply">
select max(p.c_s_time) as "time", 
p.c_p_i_id as "pid",
max(p.c_s_user_id) as "userId"
from c_service_reply p
where p.c_p_i_id = #{patientId}
group by p.c_p_i_id
</select>
</mapper>