public static string getUserList(string conditions, int rows, int page, string sort, string sortOrder) { if (rows == 0) { return("{\"total\":0,\"rows\":[]}"); } UserBLL bll = new UserBLL(); PagerInfo pagerInfo = new PagerInfo(); pagerInfo.CurrenetPageIndex = page; pagerInfo.PageSize = rows; SortInfo sortInfo = new SortInfo(sort, sortOrder); UserQueryCondition conditionModel = JsonConvert.DeserializeObject <UserQueryCondition>(conditions); DataTable dt = bll.getUserList(conditionModel, pagerInfo, sortInfo); pagerInfo.RecordCount = bll.getUserCount(conditionModel); //Json格式的要求{total:22,rows:{}} //构造成Json的格式传递 var result = new { total = pagerInfo.RecordCount, rows = dt }; return(JsonConvert.SerializeObject(result).Replace("null", "0")); }
public int getUserCount(UserQueryCondition conditValue) { StringBuilder whereSql = new StringBuilder(); string sql = @"SELECT count(1) FROM `user` "; if (!string.IsNullOrEmpty(conditValue.name)) { whereSql.Append(@" AND name like '%" + conditValue.name + "%'"); } if (!string.IsNullOrEmpty(conditValue.sex)) { whereSql.Append(@" AND sex=" + conditValue.sex); } if (!string.IsNullOrEmpty(conditValue.isDel)) { whereSql.Append(@" AND isDel=" + conditValue.isDel); } if (!string.IsNullOrEmpty(whereSql.ToString())) { sql += " WHERE " + whereSql.ToString().Substring(4); } using (var conn = new MySqlConnection(Global.strConn)) { return(conn.QuerySingle <int>(sql)); } }
/// <summary> /// 统计所有仪器 /// </summary> /// <param name="conditValue"></param> /// <param name="pagerInfo"></param> /// <param name="sortInfo"></param> /// <returns></returns> public DataTable getUserList(UserQueryCondition conditValue, PagerInfo pagerInfo, SortInfo sortInfo) { StringBuilder whereSql = new StringBuilder(); var sql = @"SELECT * FROM `user` d INNER JOIN (SELECT id FROM `user` {0} "; if (!string.IsNullOrEmpty(conditValue.name)) { whereSql.Append(@" AND name like '%" + conditValue.name + "%'"); } if (!string.IsNullOrEmpty(conditValue.sex)) { whereSql.Append(@" AND sex=" + conditValue.sex); } if (!string.IsNullOrEmpty(conditValue.isDel)) { whereSql.Append(@" AND isDel=" + conditValue.isDel); } if (!string.IsNullOrEmpty(whereSql.ToString())) { whereSql = new StringBuilder(" WHERE " + whereSql.ToString().Substring(4)); } if (!string.IsNullOrEmpty(sortInfo.SortName)) { sql += " ORDER BY " + sortInfo.SortName + " " + sortInfo.SortOrder; } sql += " LIMIT " + (pagerInfo.PageSize * (pagerInfo.CurrenetPageIndex - 1)) + "," + pagerInfo.PageSize; sql += " ) AS t USING(id);"; var SqlCondit = string.Format(sql, whereSql.ToString()); using (var conn = new MySqlConnection(Global.strConn)) { MySqlDataAdapter adapter = new MySqlDataAdapter(SqlCondit, conn); DataSet ds = new DataSet(); adapter.Fill(ds); return(ds.Tables[0]); } }
//从界面获取查询条件 private UserQueryCondition GetCodition() { var condition = new UserQueryCondition(); if (!string.IsNullOrEmpty(this.tbUserName.Text)) { condition.UserName = this.tbUserName.Text; } if (!string.IsNullOrEmpty(this.tbUserCode.Text)) { condition.UserCode = this.tbUserCode.Text; } if (!string.IsNullOrEmpty(this.tbCellPhone.Text)) { condition.UserCellPhone = this.tbCellPhone.Text; } if (this.cbUserStatus.SelectedIndex != 0) { condition.UserStatus = this.cbUserStatus.SelectedValue; } return(condition); }
public int getUserCount(UserQueryCondition conditValue) { return(dal.getUserCount(conditValue)); }
public DataTable getUserList(UserQueryCondition conditValue, PagerInfo pagerInfo, SortInfo sortInfo) { //conditValue.ProductSeries = Common.UIdataToDB(conditValue.ProductSeries); return(dal.getUserList(conditValue, pagerInfo, sortInfo)); }
public JsonResult GetUserList(UserQueryConditionModel model) { UserQueryCondition condition = new UserQueryCondition() { UserId = model.UserId, IsEnable = model.IsEnable, Mobile = model.Mobile, PageIndex = model.PageIndex, PageSize = model.PageSize, DisplayName = model.DisplayName }; if (!string.IsNullOrWhiteSpace(model.RegistrationTime)) { var arr = model.RegistrationTime.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries); if (arr.Length == 1) { if (arr[0] == "0") { // 今天 condition.StartRegistrationTime = DateTime.Now; } else { // 90天以上 condition.EndRegistrationTime = DateTime.Now.AddDays(-int.Parse(arr[0])); } } else if (arr.Length == 2) { condition.StartRegistrationTime = DateTime.Now.AddDays(-int.Parse(arr[0])); condition.EndRegistrationTime = DateTime.Now.AddDays(-int.Parse(arr[1])); } } if (!string.IsNullOrWhiteSpace(model.PurchaseCount)) { var arr = model.PurchaseCount.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries); if (arr.Length == 1) { if (arr[0] == "10") { // 10次以上 condition.EndPurchaseCount = int.Parse(arr[0]); } else { // 单个多少次 condition.StartPurchaseCount = int.Parse(arr[0]); } } else if (arr.Length == 2) { condition.StartPurchaseCount = int.Parse(arr[0]); condition.EndPurchaseCount = int.Parse(arr[1]); } } if (!string.IsNullOrWhiteSpace(model.TotalLotteryMoney)) { var arr = model.TotalLotteryMoney.Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries); if (arr.Length == 1) { condition.EndTotalLotteryMoney = int.Parse(arr[0]); } else if (arr.Length == 2) { condition.StartTotalLotteryMoney = int.Parse(arr[0]); condition.EndTotalLotteryMoney = int.Parse(arr[1]); } } var list = GlobalCache.ExternalClient.GetUserListForAdmin(condition); LayuiGridResult ret = new LayuiGridResult(); ret.code = 0; ret.count = list.TotalCount; if (list.Users != null && list.Users.Count > 0) { ret.data = list.Users.Select(p => { return(new { RowId = p.RowId, DisplayName = p.DisplayName, IsEnable = p.IsEnable, LastLotteryTime = p.LastLotteryTime, Mobile = p.Mobile, OrderCount = p.OrderCount, RegTime = p.RegTime, TotalAssets = p.TotalAssets.ToString("c"), TotalLotteryMoney = p.TotalLotteryMoney.ToString("c"), UserId = p.UserId, UserType = p.UserType }); }); } return(Json(ret, JsonRequestBehavior.AllowGet)); }