wlyy2.0/src/main/java/com/yihu/wlyy/repository/consult/ConsultTeamDao.java

/*******************************************************************************
 * Copyright (c) 2005, 2014 springside.github.io
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 *******************************************************************************/
package com.yihu.wlyy.repository.consult;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.jaxb.PageAdapter;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.yihu.wlyy.entity.consult.ConsultTeam;

import java.util.List;

public interface ConsultTeamDao extends PagingAndSortingRepository<ConsultTeam, Long>, JpaSpecificationExecutor<ConsultTeam> {
	// 根據consult查詢咨询记录
	ConsultTeam findByConsult(String consult);

	@Query("select a from ConsultTeam a where a.patient = ?1 and a.del = '1' and a.status = 0")
	List<ConsultTeam> findUnfinishedConsult(String patient);

	// 统计未完成的数量
	@Query("select count(1) from ConsultTeam a where a.patient = ?1 and a.status = 0 and a.del = '1'and a.type=?2")
	int countByPatient(String patient,Integer signType);

	// 被指定且未结束列表
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.del = '1' and b.del = '1' and a.type<3")
	Page<ConsultTeam> findDoctorList(String doctor, Pageable pageRequest);

	// 被指定且未结束列表
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.id < ?2 and a.del = '1' and b.del = '1' and a.type<3")
	Page<ConsultTeam> findDoctorList(String doctor, long id, Pageable pageRequest);

	// 被指定且未结束列表
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.status = 0 and a.del = '1' and b.del = '1' and a.type<3 ")
	Page<ConsultTeam> findDoctorPointList(String doctor, Pageable pageRequest);

	// 被指定且未结束列表
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.id < ?2 and a.status = 0 and a.del = '1' and b.del = '1' and a.type<3 ")
	Page<ConsultTeam> findDoctorPointList(String doctor, long id, Pageable pageRequest);

	// 被指定且未结束列表
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.id < ?2 and a.status = 0 and a.del = '1' and b.del = '1' and (a.type = 1 or a.type = 2 or a.type = 6) ")
	Page<ConsultTeam> findDoctorAllList(String doctor, long id, Pageable pageRequest);

	// 被指定且未结束列表
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.status = 0 and a.del = '1' and b.del = '1' and (a.type = 1 or a.type = 2 or a.type = 6) ")
	Page<ConsultTeam> findDoctorAllList(String doctor, Pageable pageRequest);

	// 医生参与且未结束列表
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.status = 0 and b.reply = 1 and a.del = '1' and b.del = '1' and a.type<3")
	Page<ConsultTeam> findDoctorJoinList(String doctor, Pageable pageRequest);

	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.id < ?2 and a.status = 0 and b.reply = 1 and a.del = '1' and b.del = '1' and a.type<3 ")
	Page<ConsultTeam> findDoctorJoinList(String doctor, long id, Pageable pageRequest);

	// 医生参与并已结束列表
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.status = 1 and a.del = '1' and b.del = '1' and a.type<3")
	Page<ConsultTeam> findDoctorFinishList(String doctor, Pageable pageRequest);

	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and b.to = ?1 and a.id < ?2 and a.status = 1 and a.del = '1' and b.del = '1' and a.type<3")
	Page<ConsultTeam> findDoctorFinishList(String doctor, long id, Pageable pageRequest);

	// 医生关闭三师咨询
	@Modifying
	@Query("update ConsultTeam a set a.status = 1 where a.consult = ?1")
	int updateStatusByConsult(String consult);

	@Modifying
	@Query("update ConsultTeam a set a.doctorRead = 0 where a.consult = ?1")
	int updateReadedByConsult(String consult);

	/**
	 * 解约时结束家庭咨询
	 *
	 * @param patient
	 * @return
	 */
	@Modifying
	@Query("update ConsultTeam a set a.status = 1 where a.patient =?1 and a.type = 2")
	int updateStatusByPatient(String patient);

	// 取消三师咨询
	@Modifying
	@Query("update ConsultTeam a set a.status = -1 where a.consult = ?1 and a.status <> 1")
	int cancel(String consult);

	// 患者咨询列表(未结束)
	@Query("select a from ConsultTeam a where a.patient = ?1 and a.status=0 and del = '1'")
	Page<ConsultTeam> findNotFinishedBypatient(String patient, Pageable pageRequest);

	@Query("select a from ConsultTeam a where a.patient = ?1 and a.id < ?2 and a.status=0 and del = '1'")
	Page<ConsultTeam> findNotFinishedBypatient(String patient, long id, Pageable pageRequest);

	// 患者咨询列表(已结束)
	@Query("select a from ConsultTeam a where a.patient=?1 and a.status=1 and del = '1'")
	Page<ConsultTeam> findFinishedBypatient(String patient, Pageable pageRequest);

	@Query("select a from ConsultTeam a where a.patient=?1 and a.id < ?2 and a.status=1 and del = '1'")
	Page<ConsultTeam> findFinishedBypatient(String patient, long id, Pageable pageRequest);

	// 患者咨询列表(已取消)
	@Query("select a from ConsultTeam a where a.patient=?1 and a.status=-1 and del = '1'")
	Page<ConsultTeam> findCancelBypatient(String patient, Pageable pageRequest);

	@Query("select a from ConsultTeam a where a.patient=?1 and a.id < ?2 and a.status=-1 and del = '1'")
	Page<ConsultTeam> findCancelBypatient(String patient, long id, Pageable pageRequest);

	// 医生未读数量+1
	@Modifying
	@Query("update ConsultTeam a set a.doctorRead = a.doctorRead + 1 where a.consult = ?1")
	int increaseDoctorRead(String consult);

	// 患者未读数量+1
	@Modifying
	@Query("update ConsultTeam a set a.patientRead = a.patientRead + 1 where a.consult = ?1")
	int increasePatientRead(String consult);

	// 清空医生未读数量
	@Modifying
	@Query("update ConsultTeam a set a.doctorRead = 0 where a.consult = ?1")
	int clearDoctorRead(String consult);

	// 清空患者未读数量
	@Modifying
	@Query("update ConsultTeam a set a.patientRead = 0 where a.consult = ?1")
	int clearPatientRead(String consult);

	// 更新患者评价标识
	@Modifying
	@Query("update ConsultTeam a set a.commentContent = ?2, a.commentStar = ?3 where a.consult = ?4")
	int updateComment(String commentContent, double commentStar, String consult);

	// 查询医生所有未读的消息总数
	@Query("SELECT SUM(a.doctorRead) FROM ConsultTeam a, ConsultTeamDoctor b WHERE a.consult = b.consult AND b.to = ?1 AND a.del = '1' AND a.status = 0 AND a.type <> 6")
	int amountAllDoctorUnread(String doctor);

	// 查询医生所有未读的消息总数
	@Query("SELECT SUM(a.doctorRead) FROM ConsultTeam a, ConsultTeamDoctor b WHERE a.consult = b.consult AND b.to = ?1 AND a.del = '1' AND a.status = 0 AND a.type = 6")
	int amountAlFamouslDoctorUnread(String doctor);

	// 查询医生所有未读的消息总数
	@Query("SELECT a.name,b.czrq FROM ConsultTeam a, ConsultTeamDoctor b WHERE a.consult = b.consult AND b.to = ?1 AND a.del = '1' AND a.status = 0 AND a.type <> 6 order by b.czrq desc")
	Page<Object> AllDoctorUnreadLast(String doctor,Pageable pageRequest);

	// 查询医生所有未读的消息总数
	@Query("SELECT a.name,b.czrq FROM ConsultTeam a, ConsultTeamDoctor b WHERE a.consult = b.consult AND b.to = ?1 AND a.del = '1' AND a.status = 0 AND a.type = 6 order by b.czrq desc")
	Page<Object> AllDoctorFamousUnreadLast(String doctor,Pageable pageRequest);

	// 查询带未读数量的所有健康咨询列表
	@Query("SELECT a FROM ConsultTeam a, ConsultTeamDoctor b WHERE a.consult = b.consult AND b.to = ?1 AND a.status = 0 and a.type< 3")
	Page<ConsultTeam> findListByUnreadDoctor(String doctor, Pageable pageRequest);

	// 查询带未读数量的所有健康咨询列表
	@Query("SELECT a FROM ConsultTeam a, ConsultTeamDoctor b WHERE a.consult = b.consult AND b.to = ?1 and a.id < ?2 AND a.status = 0 and a.type<3")
	Page<ConsultTeam> findListByUnreadDoctor(String doctor, long id, Pageable pageRequest);

	//找出当日的咨询量
	@Query("SELECT a FROM ConsultTeam a WHERE a.type=2 and unix_timestamp(a.czrq)>=unix_timestamp(?1) and unix_timestamp(a.czrq)<unix_timestamp(?2) ")
	List<ConsultTeam> findByCzrqyYesterday(String yesterday,String now);
	//查找
	@Query("SELECT a FROM ConsultTeam a WHERE a.patient=?1 and a.status=0 and a.del=1 and a.type=6")
	ConsultTeam findFamousConsultByPatient(String uid);
	//名医咨询 -全部
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.id < ?2 and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorAllList(String uid, long id, Pageable pageRequest);
	//名医咨询 -全部
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1  and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorAllList(String uid, Pageable pageRequest);
	//名医咨询 -全部 带symptoms
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.id < ?2 and a.symptoms like ?3 and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorAllList(String uid, long id,String title, Pageable pageRequest);
	//名医咨询 -全部 带symptoms
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.symptoms like ?2  and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorAllList(String uid,String title, Pageable pageRequest);
	//名医咨询 -根据status
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.id < ?2 and a.status = ?3 and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorDoingList(String uid, long id,Integer type, Pageable pageRequest);
	//名医咨询 -根据status
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.status = ?2  and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorDoingList(String uid,Integer type, Pageable pageRequest);
	//名医咨询 -根据status 带symptoms
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.id < ?2 and a.symptoms like ?3 and a.status = ?4 and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorDoingList(String uid, long id,String title,Integer type, Pageable pageRequest);
	//名医咨询 -根据status 带symptoms
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.symptoms like ?2 and a.status = ?3 and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorDoingList(String uid,String title,Integer type, Pageable pageRequest);
	@Query("select a from ConsultTeam a where  a.type=?3  and a.del = '1' and a.patient=?1 and a.doctor=?2 and  a.status=0 ")
	ConsultTeam findByParientCodeAndSignTypeAndDoctor(String patientCode, String doctor, int s);

	//名医咨询 -全部 带symptoms 未处理、未回复
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.id < ?2 and a.symptoms like ?3 and a.status = 0 and (a.doctorRead > 0 or b.reply <> 1) and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorUnReplyReadList(String uid, long id,String title, Pageable pageRequest);

	//名医咨询 -全部 带symptoms 处理、未回复
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.symptoms like ?2 and a.status = 0 and (a.doctorRead > 0 or b.reply <> 1) and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorUnReplyReadList(String uid,String title, Pageable pageRequest);

	//名医咨询 -全部 处理、未回复
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.id < ?2 and a.status = 0 and (a.doctorRead > 0 or b.reply <> 1) and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorUnReplyReadNoTitleList(String uid, long id, Pageable pageRequest);

	//名医咨询 -全部
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.status = 0 and (a.doctorRead > 0 or b.reply <> 1) and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorUnReplyReadNoTitleList(String uid, Pageable pageRequest);

	//名医咨询 -全部 带symptoms 进行中
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.id < ?2 and a.symptoms like ?3 and a.status = 0 and b.reply = 1 and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorIngList(String uid, long id,String title, Pageable pageRequest);

	//名医咨询 -全部 带symptoms 进行中
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.symptoms like ?2 and a.status = 0 and  b.reply = 1 and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorIngList(String uid,String title, Pageable pageRequest);

	//名医咨询 -全部 进行中
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.id < ?2 and a.status = 0 and  b.reply = 1 and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorIngNoTitleList(String uid, long id, Pageable pageRequest);

	//名医咨询 -全部 进行中
	@Query("select a from ConsultTeam a, ConsultTeamDoctor b where a.consult = b.consult and a.type=6 and b.to = ?1 and a.status = 0 and b.reply = 1 and a.del = '1' and b.del = '1'")
	Page<ConsultTeam> findFamousDoctorIngNoTitleList(String uid, Pageable pageRequest);

    // 最近24小时内未回复的项目
    @Query("FROM ConsultTeam a where CURRENT_TIME - a.czrq < 86400 and a.status = 0 order by a.czrq desc")
    Page<ConsultTeam> getUnresponsedConsultIn24Hours(Pageable pageable);

    // 更新超过24小时未回复的项目，多加一分钟，防止过于频繁更新
    @Modifying
    @Query("UPDATE ConsultTeam a set a.status = -2 where CURRENT_TIME - a.czrq > 87840")
    void updateUnresponsedConsultOver24Hours();
}