public ActionResult GetOrderList(FinanceOrderParameter parameter)
{
PagedResult <FinanceOrder> orderList = orderService.GetFinanceOrderList(parameter);
ViewData["memberList"] = _memberService.GetMembersList();
return(View(orderList));
}
/// <summary>
/// 获取财务审核订单列表(Admin)
/// </summary>
/// <returns></returns>
public PagedResult <FinanceOrder> GetFinanceOrderList(FinanceOrderParameter parameter)
{
StringBuilder builder = new StringBuilder(" where 1 = 1");
if (!string.IsNullOrEmpty(parameter.MemberID))
{
builder.AppendFormat(" and Members.MemberID = {0}", parameter.MemberID);
}
builder.Append($" and OrderCode like '%{parameter.OrderCode}%'");
if (!string.IsNullOrEmpty(parameter.Status))
{
builder.Append($" and orders.Status = {parameter.Status}");
}
string innerSql = "select Orders.PayTime,orders.OrderCode,members.UserType,members.MemberName,"
+ " (select COUNT(*) from OrderDetails where OrderDetails.OrderCode = Orders.OrderCode) payUserCount,"
+ " orders.PaymentMethod,"
+ " (select SUM(OrderDetails.SocialSecurityAmount * OrderDetails.SocialSecuritypayMonth + OrderDetails.SocialSecurityServiceCost + OrderDetails.SocialSecurityFirstBacklogCost + OrderDetails.SocialSecurityBuCha + OrderDetails.AccumulationFundAmount * OrderDetails.AccumulationFundpayMonth + OrderDetails.AccumulationFundServiceCost + OrderDetails.AccumulationFundFirstBacklogCost) from OrderDetails where OrderDetails.OrderCode = orders.OrderCode) Amounts,"
+ " orders.Status"
+ " from[Order] orders"
+ " left join Members on orders.MemberID = Members.MemberID"
+ $" {builder.ToString()}";
string sql = $"select * from (select ROW_NUMBER() OVER(ORDER BY t.PayTime desc )AS Row,t.* from"
+ $" ({innerSql}) t) tt"
+ " WHERE tt.Row BETWEEN @StartIndex AND @EndIndex";
List <FinanceOrder> orderList = DbHelper.Query <FinanceOrder>(sql, new
{
StartIndex = parameter.SkipCount,
EndIndex = parameter.TakeCount
});
int totalCount = DbHelper.QuerySingle <int>($"select count(0) as TotalCount from ({innerSql}) t");
return(new PagedResult <FinanceOrder>
{
PageIndex = parameter.PageIndex,
PageSize = parameter.PageSize,
TotalItemCount = totalCount,
Items = orderList
});
}
