public async Task <IActionResult> GetHotCourseAsync([FromBody] GetHotCourseRequestDto request)
{
var response = await new ConsumerBiz().GetHotCourseAsync(request);
return(Success(response));
}
/// <summary>
/// 获取首页热门课程
/// </summary>
/// <returns></returns>
public async Task <IEnumerable <GetHotCourseItemDto> > GetHotCourseAsync(GetHotCourseRequestDto request)
{
var sqlWhere = string.Empty;
if (request.BeginDate != null)
{
request.BeginDate = request.BeginDate.Value.Date;
sqlWhere = $" AND a.creation_date>=@BeginDate";
}
#region MyRegion
/*
* var sql = $@"SELECT
* a.article_guid AS course_guid,
* a.title,
* a.abstract AS summary,
* CONCAT( b.base_path, b.relative_path ) AS LogoUrl,
* a.creation_date,
* count( DISTINCT c.like_guid ) AS LikeCount,
* count( DISTINCT d.view_guid ) AS VisitCount
* FROM
* t_utility_article a
* LEFT JOIN t_utility_accessory b ON a.picture_guid = b.accessory_guid
* LEFT JOIN t_consumer_like c ON a.article_guid = c.target_guid
* AND c.`enable` = 1
* LEFT JOIN t_consumer_article_view d ON a.article_guid = d.target_guid
* AND d.`enable` = 1
* WHERE
* a.`enable` = 1
* AND a.`actcle_release_status` = 'Release'
* AND a.visible = 1
* AND a.source_type = 'Manager' {sqlWhere}
* GROUP BY
* a.article_guid,
* a.title,
* a.abstract,
* LogoUrl,
* a.creation_date,
* a.last_updated_date
* ORDER BY
* LikeCount DESC,
* VisitCount DESC,
* creation_date DESC
* limit @pageIndex,@pageSize;";
*/
#endregion
var sql = $@"SELECT
a.article_guid AS course_guid,
a.title,
a.abstract AS summary,
CONCAT( b.base_path, b.relative_path ) AS LogoUrl,
a.creation_date,
ifnull(hot.like_count,0) as LikeCount,
ifnull(hot.visit_count,0) as VisitCount
FROM
t_utility_article a
LEFT JOIN t_utility_accessory b ON a.picture_guid = b.accessory_guid
LEFT JOIN t_utility_hot hot ON hot.owner_guid = a.article_guid
AND hot.`enable` = 1
WHERE
a.`enable` = 1
AND a.`actcle_release_status` = 'Release'
AND a.visible = 1
AND a.source_type = 'Manager' {sqlWhere}
ORDER BY
LikeCount DESC,
VisitCount DESC,
creation_date DESC
limit @pageIndex,@pageSize;" ;
using (var conn = MySqlHelper.GetConnection())
{
var result = await conn.QueryAsync <GetHotCourseItemDto>(sql, new { pageIndex = (request.PageIndex - 1) * request.Take, pageSize = request.Take, request.BeginDate, enable = true, Visible = true });
return(result);
}
}
