public async Task <GetFaqsQuestionPageResponseDto> GetFaqsQuestionPageAsync(GetFaqsQuestionPageRequestDto request)
{
var sqlWhere = $@"AND a.enable=1";
if (!string.IsNullOrWhiteSpace(request.Keyword))
{
request.Keyword = $"{request.Keyword}%";
sqlWhere = $"{sqlWhere} AND a.content like @Keyword";
}
var orderBySql = $"a.creation_date {(request.IsAscending ? "Asc" : "Desc")}";
switch (request.SortBy)
{
case GetFaqsQuestionPageRequestDto.SortByEnum.Newest:
orderBySql = $"a.creation_date {(request.IsAscending ? "Asc" : "Desc")}";
break;
case GetFaqsQuestionPageRequestDto.SortByEnum.Popular:
orderBySql = $"b.visit_count {(request.IsAscending ? "Asc" : "Desc")},a.creation_date {(request.IsAscending ? "Asc" : "Desc")}";
break;
}
if (request.BeginDate != null)
{
request.BeginDate = request.BeginDate?.Date;
sqlWhere = $"{sqlWhere} AND a.creation_date > @BeginDate";
}
if (request.EndDate != null)
{
request.EndDate = request.EndDate?.AddDays(1).Date;
sqlWhere = $"{sqlWhere} AND a.creation_date < @EndDate";
}
if (request.LatestDay != 0)
{
sqlWhere = $"{sqlWhere} AND a.creation_date>DATE_SUB(NOW(),INTERVAL @LatestDay DAY)";
}
var sql = $@"
SELECT
a.question_guid,
a.user_guid,
a.reward_intergral,
a.`status`,
a.answer_num,
a.content,
a.attachment_guid_list,
a.creation_date,
b.visit_count,
a.ENABLE
FROM
t_faqs_question a
LEFT JOIN t_utility_hot b ON a.question_guid = b.owner_guid
WHERE
1 = 1 {sqlWhere}
ORDER BY
{orderBySql}" ;
return(await MySqlHelper.QueryByPageAsync <GetFaqsQuestionPageRequestDto, GetFaqsQuestionPageResponseDto, GetFaqsQuestionPageItemDto>(sql, request));
}
public async Task <IActionResult> GetFaqsQuestionPageAsync([FromQuery] GetFaqsQuestionPageRequestDto request)
{
var response = await new FaqsQuestionBiz().GetFaqsQuestionPageAsync(request);
var users = await new UserExBiz().GetListAsync(response.CurrentPage.Select(a => a.UserGuid));
foreach (var item in response.CurrentPage)
{
item.UserName = users.FirstOrDefault(a => a.UserGuid == item.UserGuid)?.UserName;
}
return(Success(response));
}