public static Ui.DataGrid GetMall_UserPointGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize) { long totalRows = 0; List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("([UserID] in (select [UserID] from [Mall_UserPoint]) or [UserID] in (select [UserID] from [Mall_UserBalance]) or [UserID] in (select [UserID] from [Mall_CouponUser]))"); if (!string.IsNullOrEmpty(Keywords)) { conditions.Add("([NickName] like @Keywords)"); parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%")); } string fieldList = "[User].* "; string Statement = " from [User] where " + string.Join(" and ", conditions.ToArray()); var user_list = GetList <User>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray(); var user_level_list = Mall_UserLevel.GetMall_UserLevels(); List <int> UserIDList = user_list.Select(p => p.UserID).ToList(); var point_list = Mall_UserAccountDetail.GetMall_UserPointBalanceList(StartTime, EndTime); var balance_list = Mall_UserAccountDetail.GetMall_UserAmountBalanceList(StartTime, EndTime); var coupon_list = Mall_CouponUser.GetMall_CouponUserListByUserIDList(UserIDList); if (StartTime > DateTime.MinValue) { coupon_list = coupon_list.Where(p => p.AddTime >= StartTime).ToArray(); } if (EndTime > DateTime.MinValue) { coupon_list = coupon_list.Where(p => p.AddTime <= EndTime).ToArray(); } var list = new List <Dictionary <string, object> >(); foreach (var user in user_list) { var my_user_level = user_level_list.FirstOrDefault(p => p.ID == user.UserLevelID); var my_point_list = point_list.Where(p => p.UserID == user.UserID).ToArray(); var my_balance_list = balance_list.Where(p => p.UserID == user.UserID).ToArray(); var my_coupon_list = coupon_list.Where(p => p.UserID == user.UserID).ToArray(); decimal CurrentConsumeAmount = my_balance_list.Sum(p => p.Total); decimal AllConsumeAmount = my_balance_list.Where(p => p.IncomeType == 1).Sum(p => p.Total); decimal CurrentPoint = my_point_list.Sum(p => p.Total); decimal AllPoint = my_point_list.Where(p => p.IncomeType == 1).Sum(p => p.Total); decimal CurrentFuShunQuan = coupon_list.Where(p => !p.IsUsed).ToArray().Length; decimal AllFuShunQuan = coupon_list.ToArray().Length; var dic = new Dictionary <string, object>(); dic["NickName"] = string.IsNullOrEmpty(user.NickName) ? user.LoginName : user.NickName; dic["MemberLevel"] = my_user_level == null ? "普通用户" : "合伙人用户(" + my_user_level.Name + ")"; dic["CurrentConsumeAmount"] = CurrentConsumeAmount; dic["AllConsumeAmount"] = AllConsumeAmount; dic["CurrentPoint"] = CurrentPoint; dic["AllPoint"] = AllPoint; dic["CurrentFuShunQuan"] = CurrentFuShunQuan; dic["AllFuShunQuan"] = AllFuShunQuan; list.Add(dic); } DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = list; dg.total = user_list.Length; dg.page = pageSize; return(dg); }