private static bool UseAlias(WhereModel where, string tableAlias)
{
var match = Regex.Match(where.ColumnAlias, @"year\((.*)\)");
if (match.Success)
{
where.ColumnAlias = where.ColumnAlias.Replace(match.Groups[1].Value, tableAlias + "." + match.Groups[1].Value);
return(false);
}
return(true);
}
private XElement Where([NotNull] WhereModel where)
{
switch (where.Type)
{
case WhereType.LogicalJoin:
return(Where((LogicalJoinModel) where));
case WhereType.Comparison:
return(Where((ComparisonModel) where));
}
throw new NotSupportedException($"'{where}' is not supported");
}
public void SQL_Parser_WhereModel()
{
string[] ClNs = DBS_ClNs();
string[] RVs = DBS_RVs();
DataChar.LogicChar[] Logics = DBS_Logics();
DataChar.OperChar[] Opers = DBS_Opers();
Random random = new Random();
List <FieldOrderModel> FOmlist = new List <FieldOrderModel>();
List <FieldValueModel> FVmlist = new List <FieldValueModel>();
for (int i = 0; i < ClNs.Length - 1; i++)
{
FOmlist.Add(new FieldOrderModel()
{
Name = ClNs[random.Next(0, ClNs.Length)],
IsAsc = random.Next(0, 2) == 0,
});
FVmlist.Add(new FieldValueModel(Opers[random.Next(0, Opers.Length)])
{
Name = ClNs[random.Next(0, ClNs.Length)],
Value = RVs[random.Next(0, RVs.Length)],
});
}
Print.WriteLine("解析 排序模型 测试:");
Print.WriteLine(JsonHelper.SerializeObject(FOmlist));
Print.WriteLine(CreateSQL.ParserFieldOrderModel(FOmlist.ToArray()));
Print.WriteLine("测试结束");
Print.WriteLine(string.Empty);
Print.WriteLine("解析 字段值模型 测试: ");
Print.WriteLine(JsonHelper.SerializeObject(FVmlist));
//Print.WriteLine(JsonHelper.SerializeObject(CreateSQL.ParserFieldValueModel(FVmlist.ToArray())));
Print.WriteLine("测试结束");
Print.WriteLine(string.Empty);
Print.WriteLine("解析 条件模型 测试: ");
WhereModel wm = DBS_WM(4);
Print.WriteLine(JsonHelper.SerializeObject(wm));
//Print.WriteLine(CreateSQL.ParserWhereModel(wm));
Print.WriteLine("测试结束");
}
private void btnSearch_Click(object sender, EventArgs e)
{
wherelist = new List <WhereModel>();
string sqlwhere = string.Empty;
string strname = tbName.Text;
if (!string.IsNullOrEmpty(strname))
{
WhereModel model = new WhereModel();
model.left = "Tablename";
model.symbol = "LIKE";
model.right = strname;
wherelist.Add(model);
}
BindDGV(wherelist);
}
/// <summary>
/// 获取用户列表
/// </summary>
/// <returns></returns>
public JsonResult List(SearchUserListInputDto input)
{
//检查访问权限
CheckPermission();
using (ResponseResult <UserModel> result = new ResponseResult <UserModel>())
{
WhereModel wheremodel = new WhereModel()
{
RoleId = Convert.ToInt32(input.RoleId),
Province = input.Province,
City = input.City,
Region = input.Region,
PhoneNumber = input.PhoneNumber,
StartTime = Convert.ToDateTime(input.StartTime),
EndTime = Convert.ToDateTime(input.EndTime),
UserTypeId = input.UserTypeId
};
long recordCount;
var modelList = this._userService.GetUserList(input.pageIndex.Value, input.pageSize.Value, out recordCount, wheremodel);
modelList.Select(m =>
{
var addressNames = _areaRepository.GetAreaNamesBy(string.Format("{0}|{1}|{2}|{3}|{4}", m.Province, m.City, m.Region, m.Township, m.Village));
var addressNamesArray = addressNames.Split('|');
m.Province = addressNamesArray[0];
m.City = addressNamesArray[1];
m.Region = addressNamesArray[2];
m.Township = addressNamesArray[3];
m.Village = addressNamesArray[4];
return(m);
}).Count();
var usersModel = new UserModel()
{
Pager = new PagedList <string>(new string[0], input.pageIndex.Value, input.pageSize.Value, (int)recordCount),
PendingAuditList = modelList,
Wheremodel = wheremodel
};
result.IsSuccess = true;
result.Entity = usersModel;
result.TotalNums = recordCount;
result.PageIndex = input.pageIndex.Value;
result.PageSize = input.pageSize.Value;
return(new JsonResultEx(result));
}
}
private WhereModel DBS_WM(int level)
{
if (level <= 0)
{
return(null);
}
string[] ClNs = DBS_ClNs();
string[] RVs = DBS_RVs();
DataChar.LogicChar[] Logics = DBS_Logics();
DataChar.OperChar[] Opers = DBS_Opers();
Random random = new Random();
WhereModel wModel = new WhereModel(Logics[random.Next(0, Logics.Length)]);
List <FieldValueModel> FVmlist = new List <FieldValueModel>();
for (int i = 0; i < random.Next(0, level); i++)
{
FVmlist.Add(new FieldValueModel(Opers[random.Next(0, Opers.Length)])
{
Name = ClNs[random.Next(0, ClNs.Length)],
Value = RVs[random.Next(0, RVs.Length)],
});
}
wModel.FielVals = FVmlist.ToArray();
List <WhereModel> whereMs = new List <WhereModel>();
for (int i = 0; i < random.Next(0, level); i++)
{
WhereModel im = DBS_WM(level - 1);
if (CheckData.IsObjectNull(im))
{
break;
}
whereMs.Add(im);
}
wModel.Wheres = whereMs.ToArray();
return(wModel);
}
public ActionResult ExportExcelWithUserList(SearchUserListInputDto input)
{
CheckPermission(GetLoginInfo().User.Id, CurrentUrl);
RestSharp.RestClient client = new RestSharp.RestClient();
RestSharp.RestRequest request = new RestSharp.RestRequest();
client.BaseUrl = new Uri(GetCurrentUrl(this));
WhereModel wheremodel;
wheremodel = new WhereModel()
{
RoleId = Convert.ToInt32(Request["RoleId"].DefaultIfEmpty("0")),
Province = Request["Province"].DefaultIfEmpty("0"),
City = Request["City"].DefaultIfEmpty("0"),
Region = Request["Region"].DefaultIfEmpty("0"),
PhoneNumber = Request["PhoneNumber"].DefaultIfEmpty(string.Empty),
};
Dictionary <string, string> parameters;
GetSearchParameters(input.pageIndex.Value, input.pageSize.Value, out wheremodel, out parameters);
foreach (var para in parameters)
{
request.AddParameter(para.Key, para.Value.IsNullOrEmpty() ? null : para.Value);
}
if (request.Parameters.Count(p => p.Name == DataKey.UserId) == 0)
{
request.AddParameter(DataKey.UserId, GetLoginInfo().User.Id.ToString());
}
var responseResult = client.ExecuteAsGet(request, "GET");
if (responseResult.Content == "no data")
{
return(Content("<script>alert('没有符合条件的数据可被导出!');history.go(-1)</script>"));
}
var contentDispositionHeader = responseResult.Headers.First(p => p.Name == "Content-Disposition").Value.ToString().Replace(" ", string.Empty);
var attachFileName = contentDispositionHeader.Replace("attachment;filename=", string.Empty);
return(File(responseResult.RawBytes, responseResult.ContentType, attachFileName));
}
/// <summary>
/// 部分比对
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCompare_Click(object sender, EventArgs e)
{
if (!SetSettingModel())
{
return;
}
StringBuilder sb = new StringBuilder();
DataGridViewCheckBoxCell checkCell;
foreach (DataGridViewRow row in dgvList.Rows)
{
checkCell = (DataGridViewCheckBoxCell)row.Cells[0];
Boolean flag = Convert.ToBoolean(checkCell.Value);
if (flag)//选中
{
sb.Append("'").Append(row.Cells[1].Value).Append("',");
}
}
if (!string.IsNullOrEmpty(sb.ToString()))
{
wherelist = new List <WhereModel>();
WhereModel model = new WhereModel();
model.left = "Tablename";
model.symbol = "IN";
model.right = sb.ToString().TrimEnd(',');
wherelist.Add(model);
this.Enabled = false;
PgsBar pgs = new PgsBar(pgsCompare, lblprogress);
bll.scc = SetSCC();
string retmsg = bll.CompareTable(wherelist, CommonConfig.ComparePartTable, pgs);
this.Enabled = true;
MessageBox.Show(retmsg, "提示", MessageBoxButtons.OK);
}
else
{
MessageBox.Show("请先选择要比对的表", "提示", MessageBoxButtons.OK);
}
}
public JsonResult Search(Int64 userId, WhereModel model, int pageIndex = 1, int pageSize = 10)
{
using (var result = new ResponseResult <RoleVerificationViewModelWithoutPager>())
{
var whereModel = ManagementRange(userId);
model.SuppliersWhere = whereModel.SuppliersWhere;
long recordCount = 0;
var items = this.roleVerificationRepository.GetAll(pageIndex, pageSize, out recordCount, model);
items.Select(m =>
{
var addressNames = this.areaRepository.GetAreaNamesBy(string.Format("{0}|{1}|{2}", m.Province, m.City, m.Region));
var addressNamesArray = addressNames.Split('|');
m.Province = addressNamesArray[0];
m.City = addressNamesArray[1];
m.Region = addressNamesArray[2];
return(m);
}).Count();
RoleVerificationViewModelWithoutPager verificationViewModel = null;
if (items != null)
{
verificationViewModel = new RoleVerificationViewModelWithoutPager()
{
PendingAuditList = items,
};
result.TotalNums = recordCount;
result.PageIndex = pageIndex;
result.PageSize = pageSize;
result.IsSuccess = true;
result.Entity = verificationViewModel;
}
//将提交过来的条件保存在ViewBag中
//ViewBag.where = model;
return(new JsonResultEx(result));
}
}
private static void ChangeComparison(WhereModel whereModel)
{
if (@whereModel.Value.ToLower() == "null")
{
switch (@whereModel.Comparison)
{
case "=":
@whereModel.Comparison = " is ";
break;
case "<>":
@whereModel.Comparison = " is not ";
break;
}
}
else
{
if (!whereModel.Value.Contains("'") && whereModel.Value.ToLower() != "null")
{
@whereModel.Value = $"\'{@whereModel.Value.Replace("'", "''")}\'";
}
}
}
public ActionResult List(WhereModel model, int pageIndex = 1, int pageSize = 10)
{
var userId = GetLoginInfo().User.Id;
var wheremodel = new WhereModel()
{
RoleId = Convert.ToInt32(string.IsNullOrEmpty(Request["RoleId"]) ? "0" : Request["RoleId"]),
Province = string.IsNullOrEmpty(Request["Province"]) ? "0" : Request["Province"],
City = string.IsNullOrEmpty(Request["City"]) ? "0" : Request["City"],
Region = string.IsNullOrEmpty(Request["Region"]) ? "0" : Request["Region"],
PhoneNumber = string.IsNullOrEmpty(Request["PhoneNumber"]) ? "" : Request["PhoneNumber"].ToString(),
};
var str_startTime = Request["StartTime"];
var str_endTime = Request["EndTime"];
if (!string.IsNullOrEmpty(str_startTime))
{
str_startTime = str_startTime.Replace("年", "-").Replace("月", "-").Replace("日", "");
}
if (!string.IsNullOrEmpty(str_endTime))
{
str_endTime = str_endTime.Replace("年", "-").Replace("月", "-").Replace("日", "");
}
DateTime dt_now = DateTime.Now;
DateTime startTime = dt_now;
DateTime endTime = dt_now;
if (string.IsNullOrEmpty(str_startTime))
{
startTime = dt_now.AddMonths(-3);
}
else
{
startTime = DateTime.Parse(str_startTime);
}
if (string.IsNullOrEmpty(str_endTime))
{
endTime = dt_now;
}
else
{
endTime = DateTime.Parse(str_endTime);
}
wheremodel.StartTime = startTime;
wheremodel.EndTime = new DateTime(endTime.Year,
endTime.Month,
endTime.Day,
23,
59,
59);
var postParas = new Dictionary <string, string>()
{
{ "UserId", userId.ToString() },
{ "pageIndex", pageIndex.ToString() },
{ "pageSize", pageSize.ToString() },
{ "RoleId", wheremodel.RoleId.ToString() },
{ "Province", wheremodel.Province },
{ "City", wheremodel.City.ToString() },
{ "Region", wheremodel.Region.ToString() },
{ "PhoneNumber", wheremodel.PhoneNumber.ToString() },
{ "StartTime", wheremodel.StartTime.ToString() },
{ "EndTime", wheremodel.EndTime.ToString() }
};
//证书的路径
var certification = GetCertificationFilePath();
//证书的密码
var certificationPwd = GetCertificationPwd();
if (postParas.ContainsKey(DataKey.UserId) == false)
{
postParas.Add(DataKey.UserId, GetLoginInfo().User.Id.ToString());
}
var responseResult = RestSharpHelper.PostWithStandard <ResponseResult <RoleVerificationViewModelWithoutPager> >(GetPostUrl("Search"), postParas, GetCertificationFilePath(), GetCertificationPwd());
if (responseResult != null && responseResult.IsSuccess)
{
var roleVerification = new RoleVerificationViewModel();
roleVerification.Pager = new PagedList <string>(new string[0], responseResult.PageIndex, responseResult.PageSize, (int)responseResult.TotalNums);
roleVerification.PendingAuditList = responseResult.Entity.PendingAuditList;
roleVerification.Wheremodel = wheremodel;
return(View("List", roleVerification));
}
return(View());
}
public IList <VM_GET_USER_ROLE_INFO_LIST> GetUserList(int pageIndex, int pageSize, out long reocrdCount, WhereModel wheremodel)
{
using (var dbContext = new DuPont_TestContext())
{
var predicate = PredicateBuilder.True <VM_GET_USER_ROLE_INFO_LIST>();
#region 拼接where条件
//角色类别
int roletype = wheremodel.RoleId;
//省
string provincecode = wheremodel.Province;
//市
string citycode = wheremodel.City;
//区县
string regioncode = wheremodel.Region;
//手机号
string phonenumber = wheremodel.PhoneNumber;
//注册开始时间
DateTime starttime = wheremodel.StartTime;
//注册结束时间
DateTime endtime = wheremodel.EndTime;
if (roletype != 0)
{
predicate = predicate.And(p => p.RoleID == roletype);
}
if (!string.IsNullOrEmpty(phonenumber))
{
predicate = predicate.And(p => p.PhoneNumber == phonenumber);
}
//if (starttime.ToString() != "0001/1/1 0:00:00" && endtime.ToString() != "0001/1/1 0:00:00")
// predicate = predicate.And(p => p.CreateTime >= starttime && p.CreateTime <= endtime);
if (starttime != DateTime.MinValue)
{
predicate = predicate.And(p => p.CreateTime >= starttime);
}
if (endtime != DateTime.MinValue)
{
predicate = predicate.And(p => p.CreateTime <= endtime);
}
if (regioncode != "0" && regioncode.IsNullOrEmpty() == false)
{
predicate = predicate.And(p => p.Region == regioncode);
}
else
{
if (citycode != "0" && citycode.IsNullOrEmpty() == false)
{
predicate = predicate.And(p => p.City == citycode);
}
else
{
if (provincecode != "0" && provincecode.IsNullOrEmpty() == false)
{
predicate = predicate.And(p => p.Province == provincecode);
}
}
}
//用户类型过滤
if (wheremodel.UserTypeId.HasValue)
{
predicate = predicate.And(p => p.Type == wheremodel.UserTypeId.Value);
}
#endregion
reocrdCount = 0;
List <VM_GET_USER_ROLE_INFO_LIST> userList = null;
var listQuery = dbContext.VM_GET_USER_ROLE_INFO_LIST.Where(predicate)
.OrderByDescending(p => p.CreateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).Future();
var totalCountQuery = dbContext.VM_GET_USER_ROLE_INFO_LIST.Where(predicate).FutureCount();
reocrdCount = totalCountQuery.Value;
if (reocrdCount > 0)
{
var userDataList = listQuery.ToList();
if (userDataList != null)
{
userList = new List <VM_GET_USER_ROLE_INFO_LIST>();
foreach (var user in userDataList)
{
userList.Add(new VM_GET_USER_ROLE_INFO_LIST
{
Id = user.Id,
UserId = user.UserId,
UserName = user.UserName,
PhoneNumber = user.PhoneNumber,
Province = user.Province,
City = user.City,
Region = user.Region,
Township = user.Township,
Village = user.Village,
CreateTime = user.CreateTime,
Land = user.Land,
RoleID = user.RoleID,
Type = user.Type
});
}
return(userList);
}
}
return(new List <VM_GET_USER_ROLE_INFO_LIST>());
}
}
public WhereModel ManagementRange(Int64 userId)
{
var whereModel = new WhereModel()
{
RoleId = 0,
PhoneNumber = string.Empty,
Province = "0",
City = "0",
Region = "0",
StartTime = DateTime.Now,
EndTime = DateTime.Now
};
var str_startTime = Request["StartTime"];
var str_endTime = Request["EndTime"];
DateTime startTime = DateTime.Now;
DateTime endTime = DateTime.Now;
if (string.IsNullOrEmpty(str_startTime) || !DateTime.TryParse(str_startTime, out startTime))
{
startTime = DateTime.Now.AddMonths(-3);
}
if (string.IsNullOrEmpty(str_endTime) || !DateTime.TryParse(str_endTime, out endTime))
{
endTime = DateTime.Now;
}
whereModel.StartTime = startTime;
whereModel.EndTime = endTime;
if (this.UserInfo.IsSuperAdmin)
{
return(whereModel);//如果是超级管理员 直接 弹出，
}
var UserId = userId;
var roleList = adminUserRepository.GetRoles(UserId);
if (roleList.Any(role => role.RoleID == (int)RoleType.Admin))
{
return(whereModel);//如果是管理员 直接 弹出，
}
var bo = false;
foreach (var role in roleList)
{
if (role.RoleID == (int)RoleType.Dealer)
{
bo = true;
}
}
if (bo)
{
var suppliers_SareaModel = suppliers_SareaRespository.GetManageArea(UserId);
var list = new List <string>();
if (suppliers_SareaModel != null && suppliers_SareaModel.Count > 0)//判断是否有管理区域
{
for (int i = 0; i < suppliers_SareaModel.Count; i++)
{
list.Add(suppliers_SareaModel[i].AID);
}
}
whereModel.SuppliersWhere = list;
if (whereModel.SuppliersWhere.Count == 0)
{
list.Add("aaa");
}
return(whereModel);
}
return(null);
}
/// <summary>
/// 获得审核列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="reocrdCount"></param>
/// <param name="model"></param>
/// <returns></returns>
public IList <VM_GET_PENDING_AUDIT_LIST> GetAll(int pageIndex, int pageSize, out long reocrdCount, WhereModel model)
{
using (var dbContext = new DuPont_TestContext())
{
IQueryable <VM_GET_PENDING_AUDIT_LIST> query = dbContext.VM_GET_PENDING_AUDIT_LIST.Where(vm => 1 == 1);
if (model != null)
{
if (model.SuppliersWhere != null && model.SuppliersWhere.Count > 0)
{
var predicate = PredicateBuilder.False <VM_GET_PENDING_AUDIT_LIST>();
foreach (var code in model.SuppliersWhere)
{
predicate = predicate.Or(vm => vm.Province == code || vm.Region == code || vm.City == code);
}
query = query.AsExpandable().Where(predicate);
}
if (model.RoleId != 0)
{
query = query.Where(vm => vm.RoleId == model.RoleId);
}
if (!string.IsNullOrEmpty(model.PhoneNumber))
{
query = query.Where(vm => vm.PhoneNumber == model.PhoneNumber);
}
if (model.Province != "0")
{
query = query.Where(vm => vm.Province == model.Province);
}
if (model.City != "0")
{
query = query.Where(vm => vm.City == model.City);
}
if (model.Region != "0")
{
query = query.Where(vm => vm.Region == model.Region);
}
if (model.StartTime.ToString() != "00001/1/1 0:00:00" && model.EndTime.ToString() != "0001/1/1 0:00:00")
{
query = query.Where(d => d.CreateTime >= model.StartTime && d.CreateTime <= model.EndTime);
}
}
//IEnumerable<VM_GET_PENDING_AUDIT_LIST> items = dbContext.VM_GET_PENDING_AUDIT_LIST.Where(model);
//var result = (from m in items
// orderby m.CreateTime ascending
var result = (from m in query
select new
{
Id = m.Id,
AuditState = m.AuditState,
City = m.City,
CreateTime = m.CreateTime,
PhoneNumber = m.PhoneNumber,
Province = m.Province,
Region = m.Region,
RoleId = m.RoleId,
RoleName = m.RoleName,
UserId = m.UserId,
UserName = m.UserName
}).OrderByDescending(d => d.CreateTime).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
reocrdCount = query.Count();
if (result != null)
{
IList <VM_GET_PENDING_AUDIT_LIST> targetResultList = new List <VM_GET_PENDING_AUDIT_LIST>();
foreach (var items in result)
{
targetResultList.Add(new VM_GET_PENDING_AUDIT_LIST
{
Id = items.Id,
AuditState = items.AuditState,
City = items.City,
CreateTime = items.CreateTime,
UserName = items.UserName,
UserId = items.UserId,
RoleName = items.RoleName,
RoleId = items.RoleId,
Region = items.Region,
Province = items.Province,
PhoneNumber = items.PhoneNumber
});
}
return(targetResultList);
}
return(null);
}
}
private void GetSearchParameters(int pageIndex, int pageSize, out WhereModel wheremodel, out Dictionary <string, string> postParas)
{
wheremodel = new WhereModel()
{
RoleId = Convert.ToInt32(Request["RoleId"].DefaultIfEmpty("0")),
Province = Request["Province"].DefaultIfEmpty("0"),
City = Request["City"].DefaultIfEmpty("0"),
Region = Request["Region"].DefaultIfEmpty("0"),
PhoneNumber = Request["PhoneNumber"].DefaultIfEmpty(string.Empty),
};
var str_startTime = Request["StartTime"];
var str_endTime = Request["EndTime"];
var str_UserTypeId = Request["UserTypeId"];
if (str_startTime.IsNullOrEmpty() == false)
{
str_startTime = str_startTime.Replace("年", "-").Replace("月", "-").Replace("日", "");
}
if (str_endTime.IsNullOrEmpty() == false)
{
str_endTime = str_endTime.Replace("年", "-").Replace("月", "-").Replace("日", "");
}
DateTime dt_now = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);
DateTime startTime = dt_now;
DateTime endTime = dt_now;
if (string.IsNullOrEmpty(str_startTime))
{
startTime = dt_now.AddMonths(-3);
}
else
{
startTime = DateTime.Parse(str_startTime);
}
if (string.IsNullOrEmpty(str_endTime))
{
endTime = dt_now;
}
else
{
endTime = DateTime.Parse(str_endTime);
}
int userTypeId = 0;
if (str_UserTypeId.IsNullOrEmpty() == false && int.TryParse(str_UserTypeId, out userTypeId))
{
wheremodel.UserTypeId = int.Parse(str_UserTypeId);
}
wheremodel.StartTime = startTime;
wheremodel.EndTime = new DateTime(endTime.Year, endTime.Month, endTime.Day, 23, 59, 59);
postParas = new Dictionary <string, string>();
postParas.Add("pageIndex", pageIndex.ToString());
postParas.Add("pageSize", pageSize.ToString());
if (Request["RoleId"] == null)
{
postParas.Add("RoleId", "0");
postParas.Add("Province", "0");
postParas.Add("City", "0");
postParas.Add("Region", "0");
postParas.Add("PhoneNumber", "");
postParas.Add("StartTime", wheremodel.StartTime.ToString());
postParas.Add("EndTime", wheremodel.EndTime.ToString());
}
else
{
postParas.Add("RoleId", Request["RoleId"].DefaultIfEmpty("0"));
postParas.Add("Province", Request["Province"].DefaultIfEmpty("0"));
postParas.Add("City", Request["City"].DefaultIfEmpty("0"));
postParas.Add("Region", Request["Region"].DefaultIfEmpty("0"));
postParas.Add("PhoneNumber", Request["PhoneNumber"].DefaultIfEmpty(string.Empty));
postParas.Add("StartTime", wheremodel.StartTime.ToString());
postParas.Add("EndTime", wheremodel.EndTime.ToString());
}
if (postParas.ContainsKey(DataKey.UserId) == false)
{
postParas.Add(DataKey.UserId, GetLoginInfo().User.Id.ToString());
}
if (wheremodel.UserTypeId != null)
{
postParas.Add("UserTypeId", wheremodel.UserTypeId.Value.ToString());
}
}
public void CheckModelIsCanUse()
{
FieldValueModel fvmodel_1 = new FieldValueModel()
{
Name = "wwww",
Value = "5468"
};
Print.WriteLine("FV 两个给: " + FieldValueModel.CheckIsCanUse(fvmodel_1).ToString());
FieldValueModel fvmodel_2 = new FieldValueModel()
{
Value = "5468"
};
Print.WriteLine("FV 只给Value: " + FieldValueModel.CheckIsCanUse(fvmodel_2).ToString());
FieldValueModel fvmodel_3 = new FieldValueModel()
{
Name = "wwww",
};
Print.WriteLine("FV 只给Name: " + FieldValueModel.CheckIsCanUse(fvmodel_3).ToString());
WhereModel model_1 = null;
Print.WriteLine("1: 赋值Null: " + WhereModel.CheckIsCanUse(model_1).ToString());
WhereModel model_2 = new WhereModel();
Print.WriteLine("2: 只new: " + WhereModel.CheckIsCanUse(model_2).ToString());
WhereModel model_3 = new WhereModel()
{
Wheres = new WhereModel[] { model_2 },
};
Print.WriteLine("3: 只给了Where 但给的是 model_2: " + WhereModel.CheckIsCanUse(model_3).ToString());
WhereModel model_4 = new WhereModel()
{
FielVals = new FieldValueModel[] { fvmodel_2 },
};
Print.WriteLine("4: 只给了FielVals, 但 fvmodel_2 没有name: " + WhereModel.CheckIsCanUse(model_4).ToString());
WhereModel model_5 = new WhereModel()
{
FielVals = new FieldValueModel[] { fvmodel_3 },
};
Print.WriteLine("5: 只给了FielVals, 并且 fvmodel_3 有name: " + WhereModel.CheckIsCanUse(model_5).ToString());
WhereModel model_6 = new WhereModel()
{
Wheres = new WhereModel[] { model_5 }
};
Print.WriteLine("6: 只给了Wheres, 并且 给的是 model_5: " + WhereModel.CheckIsCanUse(model_6).ToString());
FieldOrderModel fomodel_1 = new FieldOrderModel()
{
Name = "wwww",
IsAsc = true,
};
Print.WriteLine("FO 两个给: " + FieldOrderModel.CheckIsCanUse(fomodel_1).ToString());
FieldOrderModel fomodel_2 = new FieldOrderModel()
{
IsAsc = false,
};
Print.WriteLine("FO 只给IsAsc: " + FieldOrderModel.CheckIsCanUse(fomodel_2).ToString());
FieldOrderModel fomodel_3 = new FieldOrderModel()
{
Name = "wwww",
};
Print.WriteLine("FO 只给Name: " + FieldOrderModel.CheckIsCanUse(fomodel_3).ToString());
}
public ActionResult ExportExcelWithUserList(SearchUserListInputDto input)
{
CheckPermission();
WhereModel wheremodel = new WhereModel()
{
RoleId = Convert.ToInt32(input.RoleId),
Province = input.Province,
City = input.City,
Region = input.Region,
PhoneNumber = input.PhoneNumber,
StartTime = Convert.ToDateTime(input.StartTime),
EndTime = Convert.ToDateTime(input.EndTime)
};
long recordCount;
var modelList = this._userService.GetUserList(input.pageIndex.Value, input.pageSize.Value, out recordCount, wheremodel);
var usersModel = new UserModel()
{
Pager = new PagedList <string>(new string[0], input.pageIndex.Value, input.pageSize.Value, (int)recordCount),
PendingAuditList = modelList,
Wheremodel = wheremodel
};
if (modelList != null && modelList.Count > 0)
{
var areaCodeDictionary = new Dictionary <string, string>();
modelList.Select(m =>
{
if (ValidatorAreaCode(m.Province) && !areaCodeDictionary.ContainsKey(m.Province))
{
areaCodeDictionary.Add(m.Province, string.Empty);
}
if (ValidatorAreaCode(m.City) && !areaCodeDictionary.ContainsKey(m.City))
{
areaCodeDictionary.Add(m.City, string.Empty);
}
if (ValidatorAreaCode(m.Region) && !areaCodeDictionary.ContainsKey(m.Region))
{
areaCodeDictionary.Add(m.Region, string.Empty);
}
if (ValidatorAreaCode(m.Township) && !areaCodeDictionary.ContainsKey(m.Township))
{
areaCodeDictionary.Add(m.Township, string.Empty);
}
if (ValidatorAreaCode(m.Village) && !areaCodeDictionary.ContainsKey(m.Village))
{
areaCodeDictionary.Add(m.Village, string.Empty);
}
return(m);
}).Count();
var areaCodeList = areaCodeDictionary.Keys.ToList();
var areaInfoList = _areaRepository.GetAll(p => areaCodeList.Contains(p.AID));
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
// 创建一张工作薄。
var workSheet = workbook.CreateSheet("注册会员列表");
var headerRow = workSheet.CreateRow(0);
var tableHeaderTexts = new string[] { "用户编号", "姓名", "电话", "省", "市", "区/县", "乡镇", "村", "亩数", "用户类型", "注册时间" };
ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
//生成列头
for (int i = 0; i < tableHeaderTexts.Length; i++)
{
var currentCell = headerRow.CreateCell(i);
currentCell.SetCellValue(tableHeaderTexts[i]);
currentCell.CellStyle = style;
}
var currentRoeIndex = 0;
//给用地区信息赋上说明
foreach (var user in modelList)
{
currentRoeIndex++;
var provinceInfo = areaInfoList.FirstOrDefault(p => p.AID == user.Province);
var cityInfo = areaInfoList.FirstOrDefault(p => p.AID == user.City);
var regionInfo = areaInfoList.FirstOrDefault(p => p.AID == user.Region);
var townshipInfo = areaInfoList.FirstOrDefault(p => p.AID == user.Township);
var villageInfo = areaInfoList.FirstOrDefault(p => p.AID == user.Village);
if (provinceInfo != null)
{
user.Province = provinceInfo.DisplayName;
}
else
{
user.Province = string.Empty;
}
if (cityInfo != null)
{
user.City = cityInfo.DisplayName;
}
else
{
user.City = string.Empty;
}
if (regionInfo != null)
{
user.Region = regionInfo.DisplayName;
}
else
{
user.Region = string.Empty;
}
if (townshipInfo != null)
{
user.Township = townshipInfo.DisplayName;
}
else
{
user.Township = string.Empty;
}
if (villageInfo != null)
{
user.Village = villageInfo.DisplayName;
}
else
{
user.Village = string.Empty;
}
var dataRow = workSheet.CreateRow(currentRoeIndex);
var userIdCell = dataRow.CreateCell(0);
var userNameCell = dataRow.CreateCell(1);
var phoneNumberCell = dataRow.CreateCell(2);
var provinceCell = dataRow.CreateCell(3);
var cityCell = dataRow.CreateCell(4);
var regionCell = dataRow.CreateCell(5);
var townshipCell = dataRow.CreateCell(6);
var villageCell = dataRow.CreateCell(7);
var landCell = dataRow.CreateCell(8);
var userTypeCell = dataRow.CreateCell(9);
var registryTimeCell = dataRow.CreateCell(10);
userIdCell.SetCellValue(user.UserId);
userNameCell.SetCellValue(user.UserName);
phoneNumberCell.SetCellValue(user.PhoneNumber);
provinceCell.SetCellValue(user.Province);
cityCell.SetCellValue(user.City);
regionCell.SetCellValue(user.Region);
townshipCell.SetCellValue(user.Township);
villageCell.SetCellValue(user.Village);
landCell.SetCellValue(Convert.ToString(user.Land));
userTypeCell.SetCellValue(((UserTypes)user.Type).GetDescription());
registryTimeCell.SetCellValue(user.CreateTime.ToString("yyyy.MM.dd"));
}
workbook.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=MemberUserList" + (DateTime.Now.ToString("yyyyMMddHHmmss")) + ".xls"));
Response.BinaryWrite(ms.ToArray()); workbook = null;
return(File(ms, "application/ms-excel"));
}
else
{
return(Content("no data"));
}
}
