/// <summary>
/// 会员列表
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public async Task <GetMemberPageResponseDto> GetMemberPageAsync(GetMemberPageRequestDto request)
{
var sqlWhere = $@"1 = 1 and enable=1";
if (request.BeginDate != null)
{
request.BeginDate = request.BeginDate?.Date;
sqlWhere = $"{sqlWhere} AND creation_date > @BeginDate";
}
if (request.EndDate != null)
{
request.EndDate = request.EndDate?.AddDays(1).Date;
sqlWhere = $"{sqlWhere} AND creation_date < @EndDate";
}
if (!string.IsNullOrWhiteSpace(request.Name))
{
sqlWhere = $"{sqlWhere} AND (nick_name like @Name or user_name like @Name)";
}
if (!string.IsNullOrWhiteSpace(request.Phone))
{
sqlWhere = $"{sqlWhere} AND phone like @phone";
}
var sqlOrderBy = "last_buy_date desc";
var sql = $@"
SELECT * FROM(
SELECT
a.*,
b.order_qty,
b.order_total_amount,
b.order_average,
b.last_buy_date,
c.variation
FROM
t_utility_user a
LEFT JOIN (
SELECT
user_guid,
count( 1 ) AS order_qty,
SUM( IFNULL( paid_amount, 0 ) ) AS order_total_amount,
AVG( paid_amount ) AS order_average,
MAX( creation_date ) AS last_buy_date
FROM
t_mall_order
WHERE
order_status IN ( 'Shipped', 'Received', 'Completed' ) and enable=1
GROUP BY
user_guid
) b ON a.user_guid = b.user_guid
LEFT JOIN (SELECT user_guid,SUM(variation) variation FROM t_utility_score WHERE `enable`=1 AND user_type_guid='{UserType.Consumer}' GROUP BY user_guid ) c ON a.user_guid = c.user_guid
)___T
WHERE
{sqlWhere}
ORDER BY
{sqlOrderBy}" ;
request.Name = $"%{request.Name}%";
request.Phone = $"%{request.Phone}%";
return(await MySqlHelper.QueryByPageAsync <GetMemberPageRequestDto, GetMemberPageResponseDto, GetMemberPageItemDto>(sql, request));
}
public async Task <IActionResult> GetMemberPageAsync([FromBody] GetMemberPageRequestDto request)
{
var response = await new UserBiz().GetMemberPageAsync(request);
return(Success(response));
}
