CustomerVisitedDao.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.CustomerVisitedDao">

<!-- 查询已回访客户信息 -->
<select id="findList" parameterType="com.casking.ccss.modules.cs.entity.CustomerInfo"
                   resultType="com.casking.ccss.modules.cs.entity.CustomerInfo">
  select
  c.c_p_i_id as "num",
       c.c_p_i_name as "name",
       c.c_p_i_sex as "sex",
       c.c_p_i_age as "age",
       c.c_p_i_tel as "phone",
       c.c_p_i_diagnose_type as "diagnoseType",
       c.c_p_i_office as "office",
       c.c_p_i_doctor as "doctor",
       c.c_p_i_diagnose_date as "diagnoseDate",
       c.c_p_i_code as "code",
       d.visit_state as "visitState",
       d.c_n_status as "status",
       d.note_state as "noteStatus",
       d.question_state as "questStatus",
       s.c_n_s_id as "noteSetId",
       s.c_n_s_taskType as "taskType",
       s.c_n_s_name as "taskName",
       s.c_n_s_state as "questionState",
       max( p.c_s_time) as "replyTime",   
       max( p.c_s_user_id) as "userId"
                        
       from (((c_patient_info c
       inner join c_note_info d
       on c.c_p_i_id = d.c_i_p_id)
       inner join c_note_set s
       on d.c_n_s_id = s.c_n_s_id)
       inner join c_note_reply r
       on r.c_n_s_id = s.c_n_s_id)
       inner join c_service_reply p
       on c.c_p_i_id = p.c_p_i_id
       
       where
       r.c_n_r_visittask = '1'
       and s.c_n_s_state = '1'
       and d.C_N_STATUS is not null
       and d.C_N_STATUS = r.C_N_R_NO 
       <if test="diagnoseType !=null and diagnoseType !='all' and diagnoseType !=''">
        and c.c_p_i_diagnose_type=#{diagnoseType}
       </if>
       <if test="office != null and office !='all' and office != ''">
            and c.c_p_i_office=#{office}
       </if>
       <if test="doctor != null and doctor !='all' and doctor != ''">
        and c.c_p_i_doctor=#{doctor}
       </if>
       <if test="status != null and status !='all' and status != ''">
        and d.c_n_status=#{status}
       </if>
       <if test="taskType != null and taskType !='all' and taskType != ''">
        and s.c_n_s_taskType=#{taskType}
       </if>
       <if test="taskName != null and taskName !='all' and taskName != ''">
        and s.c_n_s_name=#{taskName}
       </if>
       <if test="visitState != null">
        and (d.visit_state = '1'
        or d.visit_state = '2')
       </if>
       <if test="beginDate != null and endDate != null">
        and p.c_s_time BETWEEN #{beginDate} AND #{endDate}
       </if>
       group by 
       c.c_p_i_id,
       c.c_p_i_name,
       c.c_p_i_sex,
       c.c_p_i_age,
       c.c_p_i_tel,
       c.c_p_i_diagnose_type,
       c.c_p_i_office,
       c.c_p_i_doctor,
       c.c_p_i_diagnose_date,
       c.c_p_i_code,
       d.visit_state,
       d.c_n_status,
       d.note_state,
       d.question_state,
       s.c_n_s_id,
       s.c_n_s_taskType,
       s.c_n_s_name,
       s.c_n_s_state
       
       order by "replyTime" desc
</select>

<!-- 二级联动查询任务类型 -->
<select id="findTask" parameterType="com.casking.ccss.modules.cs.entity.CustomerInfo"
resultType="com.casking.ccss.modules.cs.entity.CustomerInfo">
select s.c_n_s_taskType as "taskType",
s.c_n_s_name as "taskName"
from c_note_set s
where s.c_n_s_taskType = #{taskType}
</select>

<!-- 三级联动就诊类型查询 -->
<select id="findDiagnoseType" parameterType="com.casking.ccss.modules.cs.entity.CustomerInfo"
resultType="com.casking.ccss.modules.cs.entity.CustomerInfo">
select d.C_D_DIAGNOSE_TYPE as "diagnoseType",
d.C_D_DEPT_NAME as "office"
from C_DEPARTMENT d
where d.C_D_DIAGNOSE_TYPE = #{diagnoseType}
</select>
                 
</mapper>