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));
}
