public async Task <IActionResult> MakeWishList([FromQuery] RequestMakeWishInfo request)
{
string activityNo = this.GetActivityNo();
var(dataList, total) = await _operatApplication.MakeWishList(activityNo, request);
return(this.MyOK(new { dataList, total }));
}
/// <summary>
/// 许愿列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task <(IEnumerable <MakeWishInfo>, int)> MakeWishList(string activityNo, RequestMakeWishInfo request)
{
int newPageIndex = (request.pageIndex - 1) * request.pageSize;
string strWhere = string.Empty;
if (!string.IsNullOrEmpty(request.wishName))
{
strWhere += " AND bas.wish_name LIKE CONCAT('%',@wishName,'%')";
}
if (!string.IsNullOrEmpty(request.mchName))
{
strWhere += " AND mch.mch_name LIKE CONCAT('%',@mchName,'%')";
}
if (!string.IsNullOrEmpty(request.tel))
{
strWhere += " AND mem.tel = @tel";
}
if (request.wishType.HasValue)
{
strWhere += " AND bas.wish_type = @wishType";
}
if (request.status.HasValue)
{
strWhere += request.status == 1 ? " AND mak.`status` in (1,4)" : " AND mak.`status` = @status";
}
if (request.exchangeStatus.HasValue)
{
strWhere += request.exchangeStatus == 3 ? " AND mak.mch_no is NULL" : " AND mak.exchange_status = @exchangeStatus";
}
if (request.startTime.HasValue && request.endTime.HasValue)
{
request.endTime = request.endTime.Value.AddDays(1).AddSeconds(-1);
strWhere += " AND mak.created_time BETWEEN @startTime AND @endTime";
}
if (request.upStartTime.HasValue && request.upEndTime.HasValue)
{
request.upEndTime = request.upEndTime.Value.AddDays(1).AddSeconds(-1);
strWhere += " AND mak.updated_time BETWEEN @upStartTime AND @upEndTime";
}
if (request.exStartTime.HasValue && request.exEndTime.HasValue)
{
request.exEndTime = request.exEndTime.Value.AddDays(1).AddSeconds(-1);
strWhere += " AND mak.exchange_time BETWEEN @exStartTime AND @exEndTime";
}
if (request.wishId > 0)
{
strWhere += " AND mak.id=@wishId";
}
string strSql = $@"SELECT
bas.wish_name wishName,
mem.nick_name nickName,
mem.tel,
mak.created_time createdTime,
bas.wish_type wishType,
bas.rules rulesStr,
bas.wish_unit wishUnit,
mak.`status`,
mak.updated_time updatedTime,
mch.mch_name mchName,
IF(mak.mch_no is NULL,NULL,mak.exchange_status) exchangeStatus,
mak.exchange_time exchangeTime,
mak.`code`,
COUNT( hel.id ) helpTotal,
mak.id wishId
FROM
t_make_wish mak
LEFT JOIN t_wish_base bas ON bas.wish_no = mak.wish_no
LEFT JOIN t_member mem ON mem.id = mak.member_id
LEFT JOIN t_merchant mch ON mch.mch_no = mak.mch_no
LEFT JOIN t_help_wish hel ON hel.make_wish_id = mak.id
WHERE
mak.activity_no = @activityNo
{strWhere}
GROUP BY
bas.wish_name,
mem.nick_name,
mem.tel,
mak.created_time,
bas.wish_type,
bas.rules,
bas.wish_unit,
mak.`status`,
mak.updated_time,
mch.mch_name,
mak.exchange_status,
mak.exchange_time,
mak.`code`,
mak.id
ORDER BY
mak.created_time ASC,
mak.id ASC
LIMIT {newPageIndex},{request.pageSize};
SELECT
COUNT( DISTINCT mak.id )
FROM
t_make_wish mak
LEFT JOIN t_wish_base bas ON bas.wish_no = mak.wish_no
LEFT JOIN t_member mem ON mem.id = mak.member_id
LEFT JOIN t_merchant mch ON mch.mch_no = mak.mch_no
LEFT JOIN t_help_wish hel ON hel.make_wish_id = mak.id
WHERE
mak.activity_no = @activityNO
{strWhere};";
var param = new DynamicParameters();
param.Add("activityNo", activityNo);
param.AddDynamicParams(request);
var(dataList, total) = await _jiakeRepository.SQLQueryWithReturnAsync <MakeWishInfo>(strSql, param);
return(dataList, total);
}
