public async Task <IActionResult> GetDoctorAnswerAndConsultListAsync(GetHospitalDoctorAnswerAndConsultPageListRequestDto requestDto)
{
requestDto.HospitalGuid = UserID;
var hospitalBiz = new HospitalManagerBiz();
var response = await hospitalBiz.GetDoctorAnswerAndConsultListAsync(requestDto);
return(Success(response));
}
/// <summary>
/// 医院医生（解答问题数、 用户咨询数、被采纳率等）列表
/// </summary>
/// <param name="requestDto"></param>
/// <returns></returns>
public async Task <GetHospitalDoctorAnswerAndConsultPageListResponseDto> GetDoctorAnswerAndConsultListAsync(GetHospitalDoctorAnswerAndConsultPageListRequestDto requestDto)
{
var rows = (List <DoctorAnswerConsultItemDto>)null;
var sql = $@"SELECT
d.doctor_guid as DoctorGuid,
u.user_name as Name,
u.phone as Phone,
(
SELECT
IFNULL(SUM(o.times),0)
FROM t_doctor_consult_statistic as o
WHERE o.doctor_guid = d.doctor_guid {SqlWhere(2, string.Empty)}
) as ConsultNumber /*咨询总次数*/,
(
SELECT
IFNULL(SUM(o.duration),0)
FROM t_doctor_online_statistic as o
WHERE o.doctor_guid = d.doctor_guid {SqlWhere(2, string.Empty)}
) as Duration /*在线总时长*/,
(
SELECT
IFNULL(SUM(f.variation),0)
FROM `t_utility_score` as f
WHERE f.user_guid = d.doctor_guid AND f.reason LIKE '%咨询%' AND f.user_type_guid = 'Doctor' AND CAST(f.creation_date AS DATE) >= @beginDate AND CAST(f.creation_date AS DATE) <= @endDate
) as Score /*总扣分*/,
(
SELECT
IFNULL(SUM(o.times), 0)
FROM
t_doctor_answer_question_statistic AS o
WHERE o.doctor_guid = d.doctor_guid {SqlWhere(2, string.Empty)}
) as AnswerQuestionNumber /*回答问题总数量*/,
(
SELECT COUNT(DISTINCT question_guid)
FROM t_faqs_answer as f
WHERE f.user_guid = d.doctor_guid AND
f.main_answer = 1 AND CAST(f.creation_date AS DATE) >= @beginDate AND CAST(f.creation_date AS DATE) <= @endDate
) as RightTimes /*问题被采纳总个数*/,
(
SELECT
v.creation_date
FROM t_utility_visit as v
WHERE v.user_guid = u.user_guid AND v.user_type = 'Doctor'
ORDER BY v.creation_date DESC LIMIT 1
) as LastLoginTime /*最后一次登录时间*/
FROM t_doctor as d
LEFT JOIN t_utility_user as u ON d.doctor_guid = u.user_guid
WHERE d.hospital_guid = @HospitalGuid AND d.`status` = 'approved' AND d.`enable` = 1";
if (!string.IsNullOrEmpty(requestDto.Name?.Trim()))
{
sql = $"{sql} AND u.user_name like '%{requestDto.Name}%'";
}
if (!string.IsNullOrEmpty(requestDto.Phone?.Trim()))
{
sql = $"{sql} AND u.phone like '%{requestDto.Phone}%'";
}
using (var conn = MySqlHelper.GetConnection())
{
var users = await conn.QueryAsync <HospitalDocorDbBasicData>
(sql, new
{
requestDto.HospitalGuid,
beginDate = requestDto.BeginDate.ToString("yyyy-MM-dd"),
endDate = requestDto.EndDate.ToString("yyyy-MM-dd")
});
if (users.Count() <= 0)
{
return(null);
}
#region 获取医生在线配置数据
var settings = Factory.GetSettings("host.json");
var presence = settings["XMPP:presence"];
var domain = settings["XMPP:domain"];
#endregion
rows = new List <DoctorAnswerConsultItemDto>();
foreach (var u in users)
{
var item = new DoctorAnswerConsultItemDto()
{
PresenceIcon = $"{presence}?jid={u.DoctorGuid}@{domain}",
Name = u.Name,
Phone = u.Phone,
LastLoginTime = u.LastLoginTime,
AnswerQuestionNumber = u.AnswerQuestionNumber,
ConsultNumber = u.ConsultNumber,
Duration = u.Duration,
Score = u.Score
};
if (u.AnswerQuestionNumber > 0)
{
item.AdopedRate = Math.Round((decimal)u.RightTimes / item.AnswerQuestionNumber, 4) * 100;
}
rows.Add(item);
}
}
//根据医生在线和在线时长排序
rows = rows.OrderByDescending(d => d.Duration).ToList();
var total = rows.Count();
var offset = (requestDto.PageIndex - 1) * requestDto.PageSize;
rows = rows.Skip(offset).Take(requestDto.PageSize).ToList();
return(new GetHospitalDoctorAnswerAndConsultPageListResponseDto()
{
CurrentPage = rows,
Total = total
});
}
