public BaseResult UploadWGFiles(NoVerifyUploadWGFiles request) { BaseResult _br = new BaseResult(); if (string.IsNullOrEmpty(request.fpath)) { throw new Exception("参数异常!"); } var _year = null == request.year ? DateTime.Now.Year : request.year; var newpath = System.Web.HttpContext.Current.Server.MapPath(request.fpath); try { //导入数据 // var dt = Logic.Common.ExcelHelper.GetDataTable(newpath); Workbook workbook = new Workbook(); workbook.Open(newpath); Cells cells = workbook.Worksheets[0].Cells; var dt = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, true); //移出表头 dt.Rows.RemoveAt(0); dt.Rows.RemoveAt(0); #region 基础数据集 //取出岗位 List <PostViewModel> postList = null; using (var dbpost = DbFactory.Open()) { var builderpost = dbpost.From <Model.Post.Post>(); postList = dbpost.Select <PostViewModel>(builderpost); } //取出adcd List <VillageViewModel> adcdList = null; using (var dbadcd = DbFactory.Open()) { var builderadcd = dbadcd.From <ADCDInfo>(); if (adcd.Length == 15 && adcd.IndexOf("000000") > 0)//登陆的是乡镇用户 { builderadcd.Where <ADCDInfo>(x => x.adcd.StartsWith(adcd.Substring(0, 9)) && x.adcd != adcd.ToString()); } else if (adcd.Length == 6 && adcd.IndexOf("00") < 0)//登陆的是县级用户 { builderadcd.Where <ADCDInfo>(x => x.adcd.StartsWith(adcd.Substring(0, 6))); } else if (adcd == GrassrootsFloodCtrlEnums.AreaCode.省级编码.ToString())//管理员 { } else { throw new Exception("登陆用户的所属行政区划编码不正确"); } adcdList = dbadcd.Select <VillageViewModel>(builderadcd); } //取出责任人 List <VillageWorkingGroupViewModel> vlist = null; using (var db = DbFactory.Open()) { var builder = db.From <ADCDInfo>(); builder.LeftJoin <ADCDInfo, VillageWorkingGroup>((x, y) => x.adcd == y.VillageADCD); if (adcd.Length == 15 && adcd.IndexOf("000000") > 0)//登陆的是乡镇用户 { builder.Where <ADCDInfo>(x => x.adcd.StartsWith(adcd.Substring(0, 9)) && x.adcd != adcd.ToString()); } else { throw new Exception("登陆用户的所属行政区划编码不正确"); } builder.Where <VillageWorkingGroup>(y => y.VillageADCD != null && y.Year == _year); builder.Select(" VillageWorkingGroup.*,ADCDInfo.adnm"); vlist = db.Select <VillageWorkingGroupViewModel>(builder); } #endregion #region 校验 int i = 4; List <ErrList> _erlist = new List <ErrList>(); foreach (DataRow dr in dt.Rows) { ErrList _el = new ErrList(); _el.rowid = i; bool _c = false; //村名匹配 var villageName = dr[0].ToString().Trim(); if (string.IsNullOrEmpty(villageName)) { _c = false; _el.msg = "第 " + i + " 行村名为空!"; } else { var A = adcdList.Where <VillageViewModel>(w => w.adnm == villageName).FirstOrDefault(); if (null == A || string.IsNullOrEmpty(A.adcd)) { _c = false; _el.msg = "第 " + i + " 行村名和系统里标注的村名不匹配!新增村名导入前请先标注<br/>"; } else { _c = true; } } //岗位匹配 var postName = dr[1].ToString().Trim(); if (string.IsNullOrEmpty(postName)) { _c = false; _el.msg = "第 " + i + " 行岗位名为空!"; } else { var B = postList.Where <PostViewModel>(w => w.PostName == postName).FirstOrDefault(); if (null == B || string.IsNullOrEmpty(B.PostName)) { _c = false; _el.msg = "第 " + i + " 行岗位名称和系统里的岗位名称不匹配!新增岗位请先到“岗位管理”栏目设置后,重新下载模板"; } else { _c = true; } } //责任人 var personName = dr[2].ToString(); if (string.IsNullOrEmpty(personName)) { _c = false; _el.msg = "第 " + i + " 行责任人为空!"; } else { _c = true; personName = dr[2].ToString().Trim(); } //手机 var handphone = dr[4].ToString(); if (string.IsNullOrEmpty(handphone)) { _el.msg = "第 " + i + " 行责任人联系方式为空!"; } else if (!ValidatorHelper.IsMobile(handphone) && !ValidatorHelper.IsTelephone(handphone)) { _el.msg = "第 " + i + " 行责任人联系方式格式错误!手机或小灵通(格式:区号'-'号码)"; } else { } //同村,同岗,同责任人验证 if (_c) { var checkPerson = vlist.Where <VillageWorkingGroupViewModel>(w => w.adnm == villageName && w.Post == postName && w.PersonLiable == personName && w.Year == _year.ToString()).FirstOrDefault(); if (null != checkPerson) { _el.msg = "第 " + i + " 行有同村,同岗,同责任人的重复数据!"; } } if (!string.IsNullOrEmpty(_el.msg)) { _erlist.Add(_el); } i++; } _br.ErrorList = _erlist; if (_br.ErrorList.Count > 0) { _br.IsSuccess = false; _br.ErrorMsg = "数据异常,请查看数据异常提醒!"; return(_br); } #endregion #region 取出数据写入实体类 foreach (DataRow dr in dt.Rows) { //实例化责任人对象 VillageWorkingGroup item = new VillageWorkingGroup(); //获取组code var A = adcdList.Where <VillageViewModel>(w => w.adnm == dr[0].ToString().Trim()).FirstOrDefault(); item.VillageADCD = A.adcd; var B = postList.Where <PostViewModel>(w => w.PostName == dr[1].ToString().Trim()).FirstOrDefault(); item.Post = B.PostName; item.PersonLiable = string.IsNullOrEmpty(dr[2].ToString()) ? "" : dr[2].ToString().Trim(); item.Position = string.IsNullOrEmpty(dr[3].ToString()) ? "" : dr[3].ToString().Trim(); item.HandPhone = string.IsNullOrEmpty(dr[4].ToString()) ? "" : dr[4].ToString().Trim(); item.Remarks = string.IsNullOrEmpty(dr[5].ToString()) ? "" : dr[5].ToString().Trim(); item.Year = _year; item.AddTime = DateTime.Now; #region 单条日志 operateLog log = new operateLog(); log.userName = RealName; log.operateTime = DateTime.Now; log.operateMsg = A.adcd + "村{" + _year + "}导入新增了工作组责任人{" + item.PersonLiable + "}的信息"; List <operateLog> listLog = new List <operateLog>(); listLog.Add(log); item.operateLog = JsonTools.ObjectToJson(listLog); #endregion if (null != AuditNums && AuditNums.Value > 1) { item.AuditNums = AuditNums.Value + 1; } //写入数据库 using (var dbvwg = DbFactory.Open()) { var builservwg = dbvwg.From <VillageWorkingGroup>(); if (dbvwg.Insert(item) == 1) { _br.IsSuccess = true; } else { throw new Exception("写入失败"); } } } #region 日志 StringBuilder sb = new StringBuilder(); sb.Append("在栏目{组织责任/行政村防汛防台工作组}下,导入数据{" + dt.Rows.Count + "}条"); _ILogHelper.WriteLog(sb.ToString(), OperationTypeEnums.新增); #endregion _br.IsSuccess = true; _br.ErrorMsg = ""; #endregion } catch (Exception ex) { _br.IsSuccess = false; _br.ErrorMsg = "数据导入异常:" + ex.Message; File.Delete(newpath); } finally { File.Delete(newpath); } return(_br); }