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

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
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.patient.SignFamily;

public interface SignFamilyDao extends PagingAndSortingRepository<SignFamily, Long>, JpaSpecificationExecutor<SignFamily> {

	@Query("select count(1) from SignFamily a where a.idcard = ?1 and a.type = 2 and a.status >= 0")
	int hasSingStatus(String idcard);

	@Query("select a from SignFamily a where a.idcard = ?1 and status = ?2 and a.type = 2")
	SignFamily findByPatientStatus(String idcard, int status);

	@Query("select a from SignFamily a where a.patient = ?1 and status = ?2 and a.type = 2")
	SignFamily findByPatientCodeStatus(String patient, int status);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type = ?2 and a.status >= 1")
	SignFamily findByPatientAndType(String patient,int type);

	SignFamily findByCode(String code);

	SignFamily findByFamilyCode(String familyCode);

	@Query("select a from SignFamily a where a.doctor = ?1 and a.patient = ?2 and a.type = 2 and a.status >= 0")
	SignFamily findByDoctorPatient(String doctor, String patient);

	@Query("select a from SignFamily a where a.doctor = ?1 and a.patient = ?2 and a.type = 1 and a.status > 0")
	SignFamily findSsSignByDoctorPatient(String doctor, String patient);

	@Query("select a from SignFamily a,DoctorTeamMember w where a.teamCode = w.team and w.del = '1' and w.memberCode = ?1 and a.patient = ?2 and a.status > 0")
	List<SignFamily> findByDoctorAndPatient(String doctor,String patient);

	@Query("select a from SignFamily a where a.doctor = ?1 and a.patient = ?2 and a.status = 1 and a.type = 2")
	SignFamily findByFamilyDoctorAndPatient(String doctor,String patient);

	@Query("select a from SignFamily a where a.doctor = ?1 and a.patient = ?2 and a.status = 1 and a.type = 1")
	SignFamily findBySanshiDoctorAndPatient(String doctor,String patient);

	@Query("select a from SignFamily a where a.doctor = ?1 and a.openid = ?2 and a.type = 2 and a.status >= 0")
	SignFamily findByDoctorOpenid(String doctor, String openid);

	@Query("select a from SignFamily a where a.doctor = ?1 and a.idcard = ?2 and a.type = 2 and a.status >= 0")
	SignFamily findByDoctorIdcard(String doctor, String idcard);

	@Query("select a from SignFamily a where a.idcard = ?1 and a.type = 2 and a.status >= 0")
	SignFamily findByIdcard(String idcard);

	@Query("select a from SignFamily a where a.idcard = ?1 and a.type =1 and a.status >= 0")
	SignFamily findSSByIdcard(String idcard);

	@Query("select a from SignFamily a where a.idcard = ?1 ")
	List<SignFamily> findAllByIdcard(String idcard);

	@Query("select a from SignFamily a where a.idcard = ?1 and a.status >= 0")
	List<SignFamily> findSSandJTByIdcard(String idcard);

	@Query("select a from SignFamily a where a.openid = ?1 and a.type = 2 and a.status >= 0")
	SignFamily findByOpenid(String openid);

	// 取消签约申请
	@Modifying
	@Query("update SignFamily a set a.status = -1 where a.patient = ?1 and a.doctor = ?2 and a.type = 2 and a.status = 0")
	void unsignByPatient(String patient, String doctor);

	// 取消签约申请
	@Modifying
	@Query("update SignFamily a set a.status = -1 where a.openid = ?1 and a.doctor = ?2 and a.type = 2 and a.status = 0")
	void unsignByOpenid(String openid, String doctor);

	// 查询医生的签约数量
	@Query("select count(1) from SignFamily a where a.doctor = ?1")
	int countByDoctor(String doctor);

	// 查询医生的签约数量
	@Query("select count(1) from SignFamily a")
	int countByDoctor();
	// 更新状态为待解约
	@Modifying
	@Query("update SignFamily a set a.status = 2 where a.patient = ?1 and a.doctor = ?2 and a.status = 1 and a.type = 2")
	int surrender(String patient, String doctor);

	// 更新待签约状态为拒绝或同意状态
	@Modifying
	@Query("update SignFamily a set a.status = ?1 where a.patient = ?2 and a.status = 0 and a.type = 2")
	int handleSign(int status, String patient);

	// 更新已签约状态为解约
	@Modifying
	@Query("update SignFamily a set a.status = -3, a.reason = ?1 where a.patient = ?2 and a.status = 2 and a.type = 2")
	int handleSurrender(String reason, String patient);

	// 拒绝解约
	@Modifying
	@Query("update SignFamily a set a.status = 1 where a.patient = ?1 and a.status = 2 and a.type = 2")
	int refuseSurrender(String patient);

	// 查询患者已生效的家庭签约
	@Query("select a from SignFamily a where a.patient = ?1 and a.type = 2 and a.status > 0")
	SignFamily findSignByPatient(String patient);

	@Modifying
	@Query("update SignFamily a set a.openid = ?1 where patient = ?2")
	int updateOpenidByPatient(String openid, String patient);

	// 查询已签约的总数
	@Query("select count(1) from SignFamily a where a.doctor = ?1 and (a.status = 1 or a.status = 2) and a.type = 2")
	int countAmountSignedByDoctor(String doctor);

	// 查询待签约总数
	@Query("select count(1) from SignFamily a where a.doctor = ?1 and a.status = 0 and a.type = 2")
	int countAmountUnsignByDoctor(String doctor);
	
	// 查询团队中的健康管理师 sf.doctorHealth,sf.doctorHealthName,
	//select sf.doctorHealth,sf.doctorHealthName,count(*) totalContract from SignFamily sf where sf.healthDoctor = ?1
	@Query("select sf.doctorHealth,sf.doctorHealthName,count(sf)  from SignFamily sf where sf.doctorHealth = ?1")
    List<Object> findTeamHealthDoctors(String healthDoctor);

	// 查询团队中的全科医生 sf.doctor,sf.doctorName,d.hosptialName,
	//select sf.doctor,sf.doctorName,d.hosptialName,count(*) totalContract from SignFamily sf left join Doctor d on sf.doctor = d.code where sf.doctor = ?1
	@Query("select sf.doctor,sf.doctorName,d.hosptialName,count(sf) from SignFamily sf, Doctor d  where ( sf.doctor is null or sf.doctor = d.code) and sf.doctor = ?1")
    List<Object> findTeamQkDoctors(String doctor);
	// 待签约 待解决 已签约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.reason,b.streetName,b.sex from SignFamily a,Patient b,DoctorTeam t,DoctorTeamMember td where a.patient=b.code and a.teamCode=t.code and t.code=td.team and td.memberCode=?1 and td.type=?2 and a.status=?3 order by a.begin desc")
	Page<Object> findToBeSignSignWebByDoctor(String doctor, int doctorType,int status, Pageable pageRequest);

	/**
	 * 查询全科医生签约患者
	 *
	 * @param doctor
	 * @param status
	 * @param pageRequest
	 * @return
	 */
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.reason,b.streetName,b.sex from SignFamily a,Patient b where a.patient=b.code and a.doctor = ?1 and a.status=?2 order by a.begin desc")
	Page<Object> findSignedPatientByQkDoctor(String doctor,int status, Pageable pageRequest);

	/**
	 * 查询健康管理师签约患者
	 *
	 * @param doctor
	 * @param status
	 * @param pageRequest
	 * @return
	 */
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.reason,b.streetName,b.sex from SignFamily a,Patient b where a.patient=b.code and a.doctorHealth = ?1 and a.status=?2 order by a.begin desc")
	Page<Object> findSignedPatientByJkDoctor(String doctor,int status, Pageable pageRequest);

	/**
	 * 查询全科医生已解约患者
	 *
	 * @param doctor
	 * @param pageRequest
	 * @return
	 */
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.reason,b.streetName,b.sex from SignFamily a,Patient b where a.patient=b.code and a.doctor = ?1 and (a.status=-3 or a.status=-4) order by a.begin desc")
	Page<Object> findUnSignedPatientByQkDoctor(String doctor,Pageable pageRequest);

	/**
	 * 查询健康管理师已解约患者
	 *
	 * @param doctor
	 * @param pageRequest
	 * @return
	 */
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.reason,b.streetName,b.sex from SignFamily a,Patient b where a.patient=b.code and a.doctorHealth = ?1 and (a.status=-3 or a.status=-4) order by a.begin desc")
	Page<Object> findUnSignedPatientByJkDoctor(String doctor,Pageable pageRequest);

	//已经解约
	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.reason,b.streetName,b.sex from SignFamily a,Patient b,DoctorTeam t,DoctorTeamMember td where a.patient=b.code and a.teamCode=t.code and t.code=td.team and td.memberCode=?1 and td.type=?2 and(a.status=-3 or a.status=-4 ) order by a.begin desc")
	Page<Object> findToBeSignSignWebByDoctor_34(String doctor, int doctorType, Pageable pageRequest);

	@Query("select a from SignFamily a where a.mobile = ?1 and a.type = 2 and a.status >= 0")
	SignFamily findByMobile(String mobile);

	@Query("select a.code,a.doctor,b.code,b.name,b.provinceName,b.cityName,b.townName,b.address,b.photo,a.status,a.id,a.applyDate,a.reason,b.streetName,b.sex from SignFamily a,Patient b where a.patient=b.code and a.doctor=?1 and a.status=?2 order by a.begin desc")
	Page<Object> findToBeSignSignWebByDoctor_0(String doctor,  int i, Pageable pageRequest);

	@Query("select count(1) from SignFamily a where a.doctorHealth = ?1 and (a.status = 1 or a.status = 2) and a.type = 2")
	int countAmountSignedByHeather(String uid);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type =1 and a.status >= 1")
	SignFamily findBySanshiPatient(String code);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type =1 and a.status >= 0")
	SignFamily findBySsPatient(String code);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type =2 and a.status >= 1")
	SignFamily findByjiatingPatient(String code);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type =2 and a.status = 1")
	SignFamily findByjiatingPatientYes(String code);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type =1 and a.status = 1")
	SignFamily findBySanshiPatientYes(String id);

	@Query("select a from SignFamily a where a.idcard = ?1 and a.type = 1 and a.status in (0,1,2,3)")
	SignFamily findByPatientSanshiSignInfo(String idCard);

	@Query("select a from SignFamily a where a.idcard = ?1 and a.type = 2 and a.status in (0,1,2,3)")
	SignFamily findByPatientFamilySignInfo(String idCard);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type =2 and a.status >=0")
	SignFamily findByjiatingPatientStatus0(String patient);
	//查找昨天的家庭签约数据
	@Query(" from SignFamily a where  a.type =2 and a.status = 1  and unix_timestamp(a.applyDate)>=unix_timestamp(?1) and unix_timestamp(a.applyDate)<unix_timestamp(?2) ")
	List<SignFamily> findByJiatingSignYesterday(String yesterday,String today);
	//查找昨天的家庭解约数据
	@Query(" from SignFamily a where  a.type =2 and a.status in (-3,-4) and unix_timestamp(a.applyUnsignDate)>=unix_timestamp(?1) and unix_timestamp(a.applyUnsignDate)<unix_timestamp(?2) ")
	List<SignFamily> findByJiatingUnSignYesterday(String yesterday,String today);
	//查找昨天的家庭待数据
	@Query(" from SignFamily a where  a.type =2 and a.status =0  and unix_timestamp(a.patientApplyDate)>=unix_timestamp(?1) and unix_timestamp(a.patientApplyDate)<unix_timestamp(?2)")
	List<SignFamily> findByJiatingWaitSignYesterday(String yesterday,String today);
	//查找昨天的家庭待改签
	@Query(" from SignFamily a where  a.type =2 and a.status =100  and unix_timestamp(a.patientApplyDate)>=unix_timestamp(?1) and unix_timestamp(a.patientApplyDate)<unix_timestamp(?2) ")
	List<SignFamily> findByJiatingChaangeSignYesterday(String yesterday, String now);
	@Query("select a from SignFamily a where a.patient = ?1 and a.type = 1 and a.status in (0,1,2)")
	SignFamily findSanshiSignByPatient(String patientCode);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type = 2 and a.status in (0,1,2)")
	SignFamily findFamilySignByPatient(String patientCode);

	@Query("select p.code, p.name, p.photo, p.birthday, p.sex, p.diseaseCondition, p.disease, p.recordAmount,a.type,a.applyDate,p.idcard from SignFamily a,Patient p where (a.doctor = ?1 or a.doctorHealth = ?1) and a.patient = p.code and a.status in (1,2,3)")
	List<Object> findDoctorSignPatients(String doctor);

	@Query("select p.code, p.name, p.photo, p.birthday, p.sex, p.diseaseCondition, p.disease, p.recordAmount,a.type,a.applyDate,p.idcard  from SignFamily a,Patient p where (a.doctor = ?1 or a.doctorHealth = ?1) and p.name like ?2 and a.patient = p.code and a.status in (1,2,3)")
	List<Object> findDoctorSignPatientsLikeName(String doctor,String name);

	@Query("select p.code, p.name, p.photo, p.birthday, p.sex, p.diseaseCondition, p.disease, p.recordAmount,a.type,a.applyDate,p.idcard  from SignFamily a,Patient p where (a.doctor = ?1 or a.doctorHealth = ?1) and a.type = ?2 and a.patient = p.code and a.status in (1,2,3)")
	List<Object> findDoctorSignPatientsBySignType(String doctor,int type);

	@Query("select p.code, p.name, p.photo, p.birthday, p.sex, p.diseaseCondition, p.disease, p.recordAmount,a.type,a.applyDate,p.idcard  from SignFamily a,Patient p where (a.doctor = ?1 or a.doctorHealth = ?1) and a.type = ?2 and p.name like ?3 and a.patient = p.code and a.status in (1,2,3)")
	List<Object> findDoctorSignPatientsByTypeName(String doctor,int type,String name);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type =2 and a.status >= 0")
	SignFamily findByPatient(String patient);

	@Query("select a from SignFamily a where a.idcard = ?1 and a.type = 1 and a.status >= 0")
	SignFamily findBySanshiIdcard(String idcard);

	@Query("select a from SignFamily a where a.patient = ?1 and a.type = ?2 and a.status > 0")
	SignFamily findSignByPatient(String patient,int type);

	@Query(" from SignFamily a where a.patient = ?1 and a.type = ?2 and (a.status=-3 or a.status=-4)  order by a.id desc")
	List<SignFamily> findLastJySignByPatient(String patient,int type);

	@Query(" from SignFamily a where  a.type =1 and unix_timestamp(a.czrq)=unix_timestamp( ?1 )  ")
	List<SignFamily> findByDate(String s);

	@Modifying
	@Query("update SignFamily set expenses_type = ?1 where patient = ?2 and status > 0 and type = 2")
	int updateExpensesType(String expensesType,String patient);

	@Query("select count(*) from SignFamily where patient = ?1 and status > 0 and type = 1")
	int countPatientSsSign(String patient);

	@Query("select count(*) from SignFamily where patient = ?1 and status > 0 and type = 2")
	int countPatientJtSign(String patient);

	// 查询患者已生效的家庭签约
	@Query("select a from SignFamily a where a.patient = ?1 and a.type = 2 and a.status = 0")
	SignFamily findSigningByPatient(String patient);
}