NPOI 上传Excel功能（三）
4.验证Excel并上传
using DC.BE.Business.SAS; using DC.BE.Business.SYS; using DC.BE.Entity.SAS; using DC.BE.Entity.Security; using DC.BE.Entity.SYS; using DC.Framework.Entity.Definition; using Kendo.Mvc.Extensions; using Kendo.Mvc.UI; using Newtonsoft.Json; using System; using System.Linq; using System.Collections.Generic; using System.Web.Mvc; using DC.BE.Entity.Extension; using DC.BE.Entity.Common; using DC.BE.Business.SYS.SYSBModel; using System.Collections; using System.Text; using DC.BE.Business.ERP; using DC.BE.Entity.ERP; using DC.BE.Business.OA.OABModel; using System.Security.Authentication; using System.Security.Cryptography; using DC.Framework.Logging; using DC.Framework.Logging.ELLAB; using DC.Framework.Entity; using System.Reflection; using System.Linq.Expressions; using System.Web; using DC.Framework.Repository.Definition; using System.IO; using System.Data; using Excel; using DC.BE.Entity.CRM; using System.Configuration; using System.Data.Entity; using System.Security.Cryptography.X509Certificates; using DC.BE.Entity.Seeds; using System.Data.SqlClient; using DC.BE.Business.ERP.BModel; using System.Transactions; using DC.BE.Business.ERP.Parts; using DC.BE.Entity.ISP; using DC.BE.Entity.OA; using DC.BE.Business.OA; namespace DC.BE.BusinessImpl.SYS { public class UploadBaseDataBusiness : IUploadBaseDataBusiness { private readonly ITsysManagementBusiness _tsysManagementBusiness; private static readonly ILogger Logger = LoggerFactory.GetLogger(typeof(TsysManagementBusiness).FullName); private readonly ITsysStoreInfoBusiness _tsysStoreInfoBusiness; private readonly ItsysCompOrgBusiness _tsysCompOrgBusiness; private readonly ItsysCompRoleBusiness _tsysCompRoleBusiness; private readonly IUnitOfWork _unitOfWork; private readonly ITsysBaseDateImportLogBusiness _TsysBaseDateImportLogBusiness; private readonly IDataContext _context; private readonly ITsysUserBusiness _tsysUserBusiness; private readonly DcContext _dcContext; //v2.00.066 李恒宇 add_s private readonly IterpPersonInfoBusiness _terpPersonInfoBusiness; //v2.00.066 李恒宇 add_e private readonly ITsysCompCodeBusiness _tsysCompCodeBusiness; private readonly ITerpPartTotolStoreInfoBusiness _terpPartTotalStoreInfoBusiness; private readonly ITsasRegCompanyBusiness _tsasRegcompanyBusiness; private readonly ITsysTransactionDefineBusiness _TsysTransactionDefineBusiness; string tempcTblName; // 临时表名，用于批量更新或删除操作，这样快 private ResultData RDS = new ResultData(); // 返回结果 private readonly ItsysCompRegionBusiness _tsysCompRegionBusiness; private readonly ITsysGetNumberBusiness _tsysGetNumberBusiness; private readonly ITsasManagementBusiness _tsasManagementBusiness; private readonly IToaApprovalTypeMntBusiness _toaApprovalTypeMntBusiness; private readonly ITsysConfigureBusiness _tsysConfigureBusiness; private readonly ITsysMessageConfigBusiness _tsysMessageConfigBusiness; public UploadBaseDataBusiness(ITsysManagementBusiness tsysManagementBusiness, ITsysStoreInfoBusiness tsysStoreInfoBusiness, ItsysCompOrgBusiness tsysCompOrgBusiness, IUnitOfWork unitOfWork, ITsysBaseDateImportLogBusiness TsysBaseDateImportLogBusiness , IDataContext context, ITsysUserBusiness tsysUserBusiness, ItsysCompRoleBusiness tsysCompRoleBusiness, DcContext dcContext, //v2.00.066 李恒宇 add_s IterpPersonInfoBusiness terpPersonInfoBusiness, //v2.00.066 李恒宇 add_e ITsysCompCodeBusiness tsysCompCodeBusiness, ITerpPartTotolStoreInfoBusiness terpPartTotalStoreInfoBusiness, ITsasRegCompanyBusiness tsasRegcompanyBusiness, ItsysCompRegionBusiness tsysCompRegionBusiness, ITsysTransactionDefineBusiness TsysTransactionDefineBusiness, ITsysGetNumberBusiness tsysGetNumberBusiness, ITsasManagementBusiness tsasManagementBusiness, IToaApprovalTypeMntBusiness toaApprovalTypeMntBusiness, ITsysConfigureBusiness tsysConfigureBusiness, ITsysMessageConfigBusiness tsysMessageConfigBusiness ) { _tsysManagementBusiness = tsysManagementBusiness; _tsysStoreInfoBusiness = tsysStoreInfoBusiness; _unitOfWork = unitOfWork; _TsysBaseDateImportLogBusiness = TsysBaseDateImportLogBusiness; _context = context; _tsysUserBusiness = tsysUserBusiness; _tsysCompOrgBusiness = tsysCompOrgBusiness; _tsysCompRoleBusiness = tsysCompRoleBusiness; _dcContext = dcContext; //v2.00.066 李恒宇 add_s _terpPersonInfoBusiness = terpPersonInfoBusiness; //v2.00.066 李恒宇 add_e _tsysCompCodeBusiness = tsysCompCodeBusiness; _terpPartTotalStoreInfoBusiness = terpPartTotalStoreInfoBusiness; _tsasRegcompanyBusiness = tsasRegcompanyBusiness; _TsysTransactionDefineBusiness = TsysTransactionDefineBusiness; _tsysCompRegionBusiness = tsysCompRegionBusiness; _tsysGetNumberBusiness = tsysGetNumberBusiness; _tsasManagementBusiness = tsasManagementBusiness; _toaApprovalTypeMntBusiness = toaApprovalTypeMntBusiness; _tsysConfigureBusiness = tsysConfigureBusiness; _tsysMessageConfigBusiness = tsysMessageConfigBusiness; } public ResultData BaseDataTemplateImport(String compId, String middlePath) { FileInfo fi = new FileInfo(middlePath); var FilePath = middlePath; //var FileName = fi.Name.Split(\'.\')[0].Split(\' \')[1]; var fileName = FilePath.Split(\'\\\').Last().Substring(0, FilePath.Split(\'\\\').Last().IndexOf(\'+\')); var FileName = fileName.Substring(36, fileName.Length - 36); // FileStream类中 String uploadResult = String.Empty; tempcTblName = "##Temp_" + FileName + "_" + Guid.NewGuid().ToString("N"); switch (FileName) { case "tsysCompOrg": uploadResult = UploadTsysCompOrg(FilePath, compId, FileName); break; case "tsysUser": uploadResult = UploadTsysUser(FilePath, compId, FileName); break; case "terpPersonInfo": uploadResult = UploadTerpPersonInfo(FilePath, compId, FileName); break; case "tsysCompRole": uploadResult = UploadTsysCompRole(FilePath, compId, FileName); break; case "tsysUserRole": uploadResult = UploadTsysUserRole(FilePath, compId, FileName); break; case "tsysStoreInfo": uploadResult = UploadTsysStoreInfo(FilePath, compId, FileName); break; case "tsysStorePartition": uploadResult = UploadTsysStorePartition(FilePath, compId, FileName); break; case "tsysStoreLocation": uploadResult = UploadTsysStoreLocation(FilePath, compId, FileName); break; case "terpSupplierInfo": uploadResult = UploadTerpSupplierInfo(FilePath, compId, FileName); break; case "tcrmCustomerInfo": uploadResult = UploadTcrmCustomerInfo(FilePath, compId, FileName); break; case "TerpWorkHourCostTypeId": uploadResult = UploadTerpWorkHourCostTypeId(FilePath, compId, FileName); break; case "TerpBaseObject": uploadResult = UploadTerpBaseObject(FilePath); break; case "terpProductInfo": uploadResult = UploadTerpProductBase(FilePath, compId, FileName); break; case "terpPartInfo": uploadResult = UploadTerpPartInfo(FilePath, compId, FileName); break; case "terpSupplyBase": uploadResult = UploadTerpSupplyBase(FilePath, compId, FileName); break; case "terpMaterialBase": uploadResult = UploadTerpMaterialBase(FilePath, compId, FileName); break; case "terpTechUseMaterialDF": uploadResult = UploadTerpTechUseMaterialDF(FilePath, compId, FileName); break; case "terpProductDesignBom": uploadResult = UploadTerpDesignBom(FilePath, compId, FileName); break; case "terpPartRelation": uploadResult = UploadTerpDesignBomPartRelation(FilePath, compId, FileName); break; case "terpPartTotolStoreInfo": uploadResult = UploadTerpPartTotolStoreInfo(FilePath, compId, FileName); break; case "terpPartInStoreInfo": uploadResult = UploadTerpPartInStoreInfo(FilePath, compId, FileName); break; case "tsysCompCode": uploadResult = UploadTsysCompCode(FilePath, compId, FileName); break; case "tsasAgentRegister": uploadResult = UploadTsasAgentRegister(FilePath, compId, FileName); break; case "tsasRegCompServiceNote": uploadResult = UploadTsasRegCompServiceNote(FilePath, compId, FileName); break; case "terpQuoteMnt": uploadResult = UploadterpQuoteMnt(FilePath, compId, FileName); break; default: break; } RDS.Msg = uploadResult; if (RDS.Msg.IndexOf("成功") > -1) { RDS.Status = 1; RDS.Data = string.Empty; } return RDS; } #region 数据类型和长度验证（模板用 可sheet页选读） public Boolean BaseDataTemplateCheckTemplate(String extion, Stream fs, string compId, string FileName, ref String logMessage, int sheetIndex = 0) { IExcelDataReader excelReader; if (extion.Equals(".xls")) { excelReader = ExcelReaderFactory.CreateBinaryReader(fs); } else { excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs); } String message = String.Empty; DataSet result = excelReader.AsDataSet(); var sheet = result.Tables[sheetIndex]; var sheetName = result.Tables[sheetIndex].TableName; List<String> dataRequired = new List<String>(); List<String> dataTypesTemp = new List<String>(); List<String> dataTypes = new List<String>(); List<String> dataLengths = new List<String>(); String[] ziMu = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL" }; Int64 rowNum = 3; bool isError = false; TsysUser User = new TsysUser(); Guid Compid = Guid.Parse(compId); TsasRegCompany company = _unitOfWork.Repository<TsasRegCompany>().Get(x => x.Id == Compid).ToList().FirstOrDefault(); var logInfo = "开始上传" + FileName + "……"; //AddMessage(logInfo, ref logMessage); AddLastMessage(ref logInfo); try { User = _unitOfWork.Repository<TsysUser>().Get(x => x.CompanyId == Compid && x.AdminRole.Equals("087002") && x.Status == 0).ToList().FirstOrDefault(); if (User == null && Compid.ToString() != "00000000-0000-0000-0000-000000000000") { string messageA = "公司" + company.CompanyName + "用户不存在"; // AddLastMessage(ref messageA); // Logger.Information(messageA); //sw.WriteLine(messageA); AddMessage(messageA, ref logMessage); if (!excelReader.IsClosed) { excelReader.Close(); } return false; } } catch (Exception ex) { Logger.Information(ex.Message); } //获取必填项List Object[] requiredCell = sheet.Rows[2].ItemArray; //获取数据类型List Object[] typeCell = sheet.Rows[0].ItemArray; //获取数据长度List Object[] lengthCell = sheet.Rows[1].ItemArray; for (int i = 0; i < requiredCell.Length; i++) { dataTypes.Add(typeCell[i].ToString()); dataRequired.Add(requiredCell[i].ToString().Contains(\'*\') ? "必填项" : String.Empty); dataLengths.Add(lengthCell[i].ToString()); } //读取excel for (int j = 3; j < sheet.Rows.Count; j++) { Object[] cells = sheet.Rows[j].ItemArray; bool isNullLine = true; for (int i = 0; i < cells.Count(); i++) { if (!string.IsNullOrEmpty(cells[i].ToString())) { isNullLine = false; break; } } if (isNullLine) { if (rowNum < sheet.Rows.Count) { rowNum++; continue; } else { break; } } //for (int i = 1; i < dataTypes.Count; i++) for (int i = 0; i < dataTypes.Count; i++) { //必填项 if (dataRequired[i] == "必填项") { if (cells[i].ToString() == "" || cells[i].ToString() == null) { string messageB = "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列," + requiredCell[i] + " 必填项未填。"; //Logger.Information(messageB); // AddLastMessage(ref messageB); AddMessage(messageB, ref logMessage); isError = true; } else { #region switch数据类型 switch (dataTypes[i]) { //日期类型数据验证 case "date": if (!_tsysManagementBusiness.CheckDate(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } break; //INT32类型数据验证 case "int": if (!_tsysManagementBusiness.CheckInt32(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } break; //INT64类型数据验证 case "bigint": if (!_tsysManagementBusiness.CheckInt64(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } break; //DECIMAL类型数据验证 case "decimal": if (_tsysManagementBusiness.CheckDecimal(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { if (!_tsysManagementBusiness.CheckDecimalLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } } else { isError = true; AddLastMessage(ref message); } break; //VARCHAR类型数据验证 case "varchar": if (_tsysManagementBusiness.CheckVarchar(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { if (!_tsysManagementBusiness.CheckLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } } else { isError = true; AddLastMessage(ref message); } break; //VARCHAR类型数据验证 case "nvarchar": if (!_tsysManagementBusiness.CheckLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } break; } #endregion } } //非必填项 else { if (cells[i].ToString() == "" || cells[i].ToString() == null) { continue; } else { #region switch数据类型 switch (dataTypes[i]) { //日期类型数据验证 case "date": if (!_tsysManagementBusiness.CheckDate(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } break; //INT32类型数据验证 case "int32": if (!_tsysManagementBusiness.CheckInt32(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } break; //INT64类型数据验证 case "int64": if (!_tsysManagementBusiness.CheckInt64(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } break; //DECIMAL类型数据验证 case "decimal": if (_tsysManagementBusiness.CheckDecimal(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { if (!_tsysManagementBusiness.CheckDecimalLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } } else { isError = true; AddLastMessage(ref message); } break; //VARCHAR类型数据验证 case "varchar": if (_tsysManagementBusiness.CheckVarchar(cells[i].ToString(), "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { if (!_tsysManagementBusiness.CheckLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } } else { isError = true; AddLastMessage(ref message); } break; //VARCHAR类型数据验证 case "nvarchar": if (!_tsysManagementBusiness.CheckLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + 1) + "行,第" + ziMu[i] + "列", ref logMessage, ref message)) { isError = true; AddLastMessage(ref message); } break; } #endregion } } } rowNum++; //else { rowNum = 0; break; } //} } //验证结果有异常 SYSE0031 if (isError) { return false; } else { return true; } //var filePath = Path.Combine(@"C:\temp", company.CompanyLoginMark.ToString() + "基础数据导入日志.txt"); //File.Delete(filePath); //using (FileStream fsm = new FileStream(filePath, FileMode.Append)) //{ // using (StreamWriter sw = new StreamWriter(fsm, System.Text.Encoding.Default)) // { // } //} } #endregion #region 公司组织机构上传 //公司组织机构上传 public String UploadTsysCompOrg(String filePath, String compId, String FileName) { String logInfo = ""; String logMessage = ""; Logger.Information("开始上传组织结构..."); #region 数据存在，不能再次插入 Guid Compid = Guid.Parse(compId); var extion = Path.GetExtension(filePath); int sheetNum = 1;//读取sheet页 TsasRegCompany company = _unitOfWork.Repository<TsasRegCompany>().Get(x => x.Id == Compid).ToList().FirstOrDefault(); TsysUser User = new TsysUser(); //共通验证 using (FileStream fs = new FileStream(filePath, FileMode.Open)) { if (!this.BaseDataTemplateCheckTemplate(extion, fs, compId, FileName, ref logMessage, sheetNum)) { return "组织结构上传失败！"; } } try { User = _unitOfWork.Repository<TsysUser>().Get(x => x.CompanyId == Compid && x.AdminRole.Equals("087002") && x.Status == 0).ToList().FirstOrDefault(); if (User == null) { logInfo = "公司" + company.CompanyName.ToString() + "用户不存在"; AddLastMessage(ref logMessage); return "组织结构上传失败！"; } } catch (Exception ex) { Logger.Information(ex.Message); } Guid UserId = User.Id; List<TsysCompOrg> updateOrgList = new List<TsysCompOrg>(); try { updateOrgList = _unitOfWork.Repository<TsysCompOrg>().Get(x => x.CompanyId == Compid && x.Status == 0).ToList(); } catch (Exception ex) { Logger.Information(ex.Message); } String CompanyName = ""; try { CompanyName = _unitOfWork.Repository<TsasRegCompany>().Get(m => m.Id == Compid).FirstOrDefault().CompanyName; } catch (Exception ex) { Logger.Information(ex.Message); } if (updateOrgList.Count > 1) { foreach (var item in updateOrgList) { if (item.OrgName != CompanyName) { using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 20, 0))) { try { _tsysCompOrgBusiness.ISPDelete(item.Id); } catch (Exception ex) { item.Status = EntityStatusEnum.Active; _tsysCompOrgBusiness.ISPUpdate(item); logInfo = "数据正在被使用，无法再次重新上传，请在功能菜单上更正数据。"; AddMessage(logInfo, ref logMessage); // AddLastMessage(ref logMessage); return "组织结构上传失败！"; } scope.Complete(); } } } } //容器准备 List<Tuple<String, String, String>> AllExcelData = new List<Tuple<String, String, String>>(); Dictionary<int, TsysCompOrg> allFixedData = new Dictionary<int, TsysCompOrg>(); List<CheckTreeBModel> AllTree = new List<CheckTreeBModel>(); List<UploadCompOrgBModel> NodeOrgs = new List<UploadCompOrgBModel>(); #endregion #region 读取excel数据 using (FileStream newFs = new FileStream(filePath, FileMode.Open)) { IExcelDataReader excelReader; if (extion.Equals(".xls")) { excelReader = ExcelReaderFactory.CreateBinaryReader(newFs); } else { excelReader = ExcelReaderFactory.CreateOpenXmlReader(newFs); } Boolean AllCheckResult = true; var sheet = excelReader.AsDataSet().Tables[sheetNum]; for (int r = 3; r < sheet.Rows.Count; r++) { Object[] currentRow = sheet.Rows[r].ItemArray; if (String.IsNullOrEmpty(currentRow[0].ToString()) && String.IsNullOrEmpty(currentRow[1].ToString()) && String.IsNullOrEmpty(currentRow[2].ToString()) ) { continue; } Tuple<String, String, String> ThisRow = new Tuple<String, String, String>( currentRow[0].ToString(), //组织机构编码 currentRow[1].ToString(), //组织名 currentRow[2].ToString() //父组织机构编码 ); AllExcelData.Add(ThisRow); AllTree.Add(new CheckTreeBModel() { Child = ThisRow.Item1, Parent = ThisRow.Item3, RowNumber = r + 1 }); } #endregion #region 父子级验证 List<CheckTreeBModel> TreeBModel = new List<CheckTreeBModel>(); TreeBModel = _tsysManagementBusiness.CheckTree(AllTree); for (int i = 0; i < AllExcelData.Count; i++) { Boolean isTrue = _tsysManagementBusiness.CheckTree(TreeBModel, AllExcelData[i].Item1, AllExcelData[i].Item3); if (!isTrue) { logInfo = "第" + AllTree[i].RowNumber.ToString() + "行" + "组织机构编码父子级不正确"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } } #endregion if (!AllCheckResult) { AddLastMessage(ref logMessage); return "组织结构上传失败！"; } #region Tree结构 Hashtable nodeOrgList = new Hashtable(); int row = 0; foreach (var item in AllExcelData) { nodeOrgList.Add(item.Item1, new UploadCompOrgBModel { ChildCode = item.Item1, Id = Guid.NewGuid(), ParentCode = item.Item3, OrgName = item.Item2, RowExcel = row++, Items = new List<UploadCompOrgBModel>() }); } Hashtable sortOrgList = new Hashtable(); foreach (UploadCompOrgBModel item in nodeOrgList.Values) if (string.IsNullOrEmpty(item.ParentCode)) NodeOrgs.Add(item); else { List<UploadCompOrgBModel> Items = ((UploadCompOrgBModel)nodeOrgList[item.ParentCode]).Items; Items.Add(item); if (sortOrgList[item.ParentCode] == null && Items.Count > 1) sortOrgList.Add(item.ParentCode, Items); } #endregion #region 获取父子级顺序的集合 allFixedData = _tsysManagementBusiness.BModelToEntity(NodeOrgs, Compid); #endregion #region Insert DataTable DT = DBHelper.GetDataSet("select * from tsysCompOrg where 1<>1"); allFixedData.ToList().ForEach(t => { DataRow drow = DT.NewRow(); drow["Id"] = t.Value.Id; drow["Status"] = 0; drow["RowVersion"] = default(byte[]); drow["CompanyId"] = compId; drow["CreatedDate"] = DateTime.Now; drow["CreatedBy"] = UserId; drow["UpdatedDate"] = DateTime.Now; drow["UpdatedBy"] = UserId; drow["OrganizationId"] = User.OrgId; drow["ParentOrgId"] = t.Value.ParentOrgId; drow["OrgCode"] = t.Value.OrgCode; drow["OrgName"] = t.Value.OrgName; drow["NodeCode"] = t.Value.NodeCode; drow["SortNo"] = t.Value.SortNo; DT.Rows.Add(drow); }); try { DBHelper.BulkInsert(DT, "tsysCompOrg"); } catch (Exception ex) { Logger.Information(ex.Message); if (String.IsNullOrEmpty(logMessage)) { return "组织结构上传失败！"; } else { AddLastMessage(ref logMessage); return "组织结构上传失败！"; } } #endregion return "组织结构上传成功！"; } } #endregion #region 系统用户管理上传 //系统用户管理上传 public String UploadTsysUser(String filePath, String compId, String FileName) { String logInfo = ""; String logMessage = ""; Logger.Information("开始上传系统用户..."); #region ready var extion = Path.GetExtension(filePath); int sheetNum = 1;//读取sheet页 Guid Compid = Guid.Parse(compId); TsasRegCompany company = _unitOfWork.Repository<TsasRegCompany>().Get(x => x.Id == Compid).ToList().FirstOrDefault(); using (FileStream fs = new FileStream(filePath, FileMode.Open)) { if (!this.BaseDataTemplateCheckTemplate(extion, fs, compId, FileName, ref logMessage, sheetNum)) { return "系统用户上传失败！"; } } DataTable Dt = new DataTable(); for (int i = 0; i < 12; i++) { Dt.Columns.Add(new DataColumn()); } List<TerpUserAndRole> AllUserAndRoleData = new List<TerpUserAndRole>(); TsysUser User = new TsysUser(); try { User = _unitOfWork.Repository<TsysUser>().Get(x => x.CompanyId == Compid && x.AdminRole.Equals("087002") && x.Status == 0).ToList().FirstOrDefault(); if (User == null) { logInfo = "公司" + company.CompanyName.ToString() + "管理员用户不存在"; AddMessage(logInfo, ref logMessage); // AddLastMessage(ref logMessage); return "系统用户上传失败！"; } } catch (Exception ex) { Logger.Information(ex.Message); } Guid UserId = User.Id; Guid OrganizationId = User.OrgId.Value; List<Int32> LogRowNumber = new List<Int32>(); #endregion #region get all value using (FileStream newFs = new FileStream(filePath, FileMode.Open)) { IExcelDataReader excelReader; if (extion.Equals(".xls")) { excelReader = ExcelReaderFactory.CreateBinaryReader(newFs); } else { excelReader = ExcelReaderFactory.CreateOpenXmlReader(newFs); } Boolean AllCheckResult = true; var sheet = excelReader.AsDataSet().Tables[sheetNum]; for (int r = 3; r < sheet.Rows.Count; r++) { Object[] currentRow = sheet.Rows[r].ItemArray; if ( String.IsNullOrEmpty(currentRow[0].ToString()) && String.IsNullOrEmpty(currentRow[1].ToString()) && String.IsNullOrEmpty(currentRow[2].ToString()) && String.IsNullOrEmpty(currentRow[3].ToString()) && String.IsNullOrEmpty(currentRow[4].ToString()) && String.IsNullOrEmpty(currentRow[5].ToString()) && String.IsNullOrEmpty(currentRow[6].ToString()) && String.IsNullOrEmpty(currentRow[7].ToString()) && String.IsNullOrEmpty(currentRow[8].ToString()) && String.IsNullOrEmpty(currentRow[9].ToString()) && String.IsNullOrEmpty(currentRow[10].ToString()) ) { continue; } #region read data row DataRow ThisRow = Dt.NewRow(); ThisRow[1] = currentRow[0].ToString();//用户名UserName ThisRow[2] = currentRow[1].ToString();//员工编号EmployeeCode ThisRow[3] = currentRow[2].ToString(); //性别Gender ThisRow[4] = currentRow[3].ToString(); //组织机构编码OrgId ThisRow[5] = currentRow[4].ToString();//电话UserTel ThisRow[6] = currentRow[5].ToString();//邮箱UserEmail ThisRow[7] = currentRow[6].ToString();//登录ID ThisRow[8] = currentRow[7].ToString();//密码 ThisRow[9] = currentRow[8].ToString();//系统角色AdminRole ThisRow[10] = currentRow[9].ToString();//角色名称 ThisRow[11] = currentRow[10].ToString();//备注 #endregion #region check repeat //员工编码check加在Excel Dt.Rows.Add(ThisRow); LogRowNumber.Add(r + 1); #endregion } #endregion #region Get all fixed data #region Get ready data List<TsysCompOrg> allOrg = _tsysManagementBusiness.GetAllSYSCompOrg(compId); String Mark = ""; try { Mark = _unitOfWork.Repository<TsasRegCompany>().Get(x => x.Id == Compid).FirstOrDefault().CompanyLoginMark; } catch (Exception ex) { Logger.Information(ex.Message); } List<TsysCompRole> AllCompRole = _tsysManagementBusiness.GetTsysCompRole(compId); //性别 var ProductiveNature = Guid.Parse("00000000-0000-0000-0000-000000000000"); List<TsysCompCode> gender = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == ProductiveNature && t.Category_Code == "001" && t.Status == EntityStatusEnum.Active).ToList(); //系统角色 var SystemRole = Guid.Parse("00000000-0000-0000-0000-000000000000"); List<TsysCompCode> adminRole = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == SystemRole && t.Category_Code == "087" && t.Status == EntityStatusEnum.Active).ToList(); #endregion for (int i = 0; i < Dt.Rows.Count; i++) { TerpUserAndRole userAndRole = new TerpUserAndRole(); userAndRole.Id = Guid.NewGuid(); userAndRole.UserName = Dt.Rows[i][1].ToString(); userAndRole.EmployeeCode = Dt.Rows[i][2].ToString(); if (!string.IsNullOrEmpty(Dt.Rows[i][3].ToString())) { TsysCompCode _gender = gender.FirstOrDefault(t => t.Item_Name.Equals(Dt.Rows[i][3].ToString())); if (_gender == null) { logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + (3) + "列" + "性别未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { userAndRole.Gender = _gender.Category_Code + _gender.Item_Code; } } if (!string.IsNullOrEmpty(Dt.Rows[i][4].ToString())) { if (allOrg != null) { TsysCompOrg org = allOrg.Where(x => !String.IsNullOrEmpty(x.OrgName) && x.OrgName.Equals(Dt.Rows[i][4].ToString())).FirstOrDefault(); if (org == null) { logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + (4) + "列" + "组织机构未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { userAndRole.OrgId = org.Id; } } else { logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + (4) + "列" + "组织机构未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } } userAndRole.UserTel = Dt.Rows[i][5].ToString(); userAndRole.UserEmail = Dt.Rows[i][6].ToString(); userAndRole.LoginId = Dt.Rows[i][7].ToString() + "@" + Mark; string psw = Dt.Rows[i][8].ToString(); string salt = CreateSalt(); string enPsw = ComputeHash(psw, salt); userAndRole.Password = psw; userAndRole.Salt = salt; userAndRole.LoginPwd = enPsw; TsysCompCode _adminRole = adminRole.FirstOrDefault(t => t.Item_Name.Equals(Dt.Rows[i][9].ToString())); if (_adminRole == null) { logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + (9) + "列" + "系统角色未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { userAndRole.AdminRole = _adminRole.Category_Code + _adminRole.Item_Code; } if (!String.IsNullOrEmpty(compId)) { userAndRole.CompanyId = Guid.Parse(compId); } userAndRole.Memo = Dt.Rows[i][11].ToString(); if (String.IsNullOrEmpty(Dt.Rows[i][10].ToString())) { TsysCompRole CompRole = AllCompRole.Where(x => !String.IsNullOrEmpty(x.Role_Name) && x.Role_Name.Equals("普通用户")).FirstOrDefault(); userAndRole.RelativeRoleId = CompRole.Id; } else { TsysCompRole CompRole = AllCompRole.Where(x => !String.IsNullOrEmpty(x.Role_Name) && x.Role_Name.Equals(Dt.Rows[i][10].ToString())).FirstOrDefault(); if (CompRole == null) { logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + (10) + "列" + "角色名称未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { userAndRole.RelativeRoleId = CompRole.Id; } } AllUserAndRoleData.Add(userAndRole); } if (!AllCheckResult) { AddLastMessage(ref logMessage); return "系统用户上传失败！"; } #endregion #region insert / update try { Guid CompId = Guid.Parse(compId); // 取得当前表中数据 List<TsysUser> _userList = _unitOfWork.Repository<TsysUser>().Get(t => t.Status == EntityStatusEnum.Active && t.CompanyId == CompId).ToList(); List<TsysUserRole> _roleList = _unitOfWork.Repository<TsysUserRole>().Get(t => t.Status == EntityStatusEnum.Active && t.CompanyId == CompId).ToList(); var userMearge = from a in AllUserAndRoleData join b in _userList on a.EmployeeCode equals b.EmployeeCode into temp from tt in temp.DefaultIfEmpty() select new { t1 = a, t2 = tt }; //用户表数据取出 var userInsert = userMearge.Where(t => t.t2 == null).Select(t => t.t1).ToList(); var userOldUpdate = userMearge.Where(t => t.t2 != null).Select(t => t.t2).ToList(); var userUpdate = userMearge.Where(t => t.t2 != null).Select(t => t.t1).ToList(); // 修改当前库为临时库 ; // 获取一个空表 DataTable userDT = DBHelper.GetDataSet("select * from TsysUser where 1<>1"); DataTable roleDT = DBHelper.GetDataSet("select * from TsysUserRole where 1<>1"); #region 批量赋值 userInsert.ForEach(t => { DataRow row = userDT.NewRow(); row["Id"] = t.Id; row["LoginId"] = String.IsNullOrEmpty(t.LoginId) ? "" : t.LoginId; row["LoginPwd"] = String.IsNullOrEmpty(t.LoginPwd) ? "" : t.LoginPwd; row["Salt"] = String.IsNullOrEmpty(t.Salt) ? "" : t.Salt; row["AdminRole"] = String.IsNullOrEmpty(t.AdminRole) ? "" : t.AdminRole; row["UserName"] = String.IsNullOrEmpty(t.UserName) ? "" : t.UserName; row["Gender"] = String.IsNullOrEmpty(t.Gender) ? "" : t.Gender; row["EmployeeCode"] = String.IsNullOrEmpty(t.EmployeeCode) ? "" : t.EmployeeCode; row["OrgId"] = t.OrgId; row["UserEmail"] = String.IsNullOrEmpty(t.UserEmail) ? "" : t.UserEmail; row["UserTel"] = String.IsNullOrEmpty(t.UserTel) ? "" : t.UserTel; row["VerificationCode"] = String.IsNullOrEmpty(t.VerificationCode) ? "" : t.VerificationCode; if (t.VerificationDate != null) row["VerificationDate"] = t.VerificationDate; row["Memo"] = String.IsNullOrEmpty(t.Memo) ? "" : t.Memo; if (t.BelongToCompanyId != null) row["BelongToCompanyId"] = t.BelongToCompanyId; else row["BelongToCompanyId"] = new Guid(); row["OrganizationId"] = OrganizationId; row["UserId"] = UserId; row["CompanyId"] = CompId; row["SourceId"] = String.IsNullOrEmpty(t.SourceId) ? "" : t.SourceId; row["Status"] = 0; row["RowVersion"] = default(byte[]); row["CreatedDate"] = DateTime.Now; row["CreatedBy"] = UserId; row["UpdatedDate"] = DateTime.Now; row["UpdatedBy"] = UserId; row["Enable"] = "036002"; userDT.Rows.Add(row); DataRow row1 = roleDT.NewRow(); row1["Id"] = Guid.NewGuid(); if (t.Id != null) row1["RelativeUserId"] = t.Id; if (t.RelativeRoleId != null) row1["RelativeRoleId"] = t.RelativeRoleId; row1["OrganizationId"] = OrganizationId; row1["UserId"] = UserId; row1["CompanyId"] = CompId; row1["SourceId"] = String.IsNullOrEmpty(t.SourceId) ? "" : t.SourceId; row1["Status"] = 0; row1["RowVersion"] = default(byte[]); row1["CreatedDate"] = DateTime.Now; row1["CreatedBy"] = UserId; row1["UpdatedDate"] = DateTime.Now; row1["UpdatedBy"] = UserId; roleDT.Rows.Add(row1); }); #endregion try { // 批量插入 DBHelper.BulkInsert(userDT, "TsysUser"); DBHelper.BulkInsert(roleDT, "TsysUserRole"); } catch (Exception ex) { Logger.Error(ex); if (String.IsNullOrEmpty(logMessage)) return "系统用户上传失败！"; else { AddLastMessage(ref logMessage); return "系统用户上传失败！"; } } try { using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 20, 0))) { var dbset = _context.Set<TsysUser>().ToList(); for (int i = 0; i < userUpdate.Count; i++) { TsysUser findReuslt = dbset.FirstOrDefault(t => t.Id.Equals(userOldUpdate[i].Id)); TerpUserAndRole updateResult = userUpdate.FirstOrDefault(t => t.EmployeeCode.Equals(userOldUpdate[i].EmployeeCode)); findReuslt.LoginPwd = updateResult.LoginPwd; findReuslt.Salt = updateResult.Salt; findReuslt.AdminRole = updateResult.AdminRole; findReuslt.UserName = updateResult.UserName; findReuslt.Gender = updateResult.Gender; findReuslt.EmployeeCode = updateResult.EmployeeCode; findReuslt.OrgId = updateResult.OrgId; findReuslt.UserEmail = updateResult.UserEmail; findReuslt.UserTel = updateResult.UserTel; findReuslt.Memo = updateResult.Memo; findReuslt.LoginId = updateResult.LoginId; findReuslt.UpdatedDate = DateTime.Now; } _context.SaveChanges(); scope.Complete(); } } catch (Exception ex) { Logger.Error(ex); if (String.IsNullOrEmpty(logMessage)) return "系统用户上传失败！"; else { AddLastMessage(ref logMessage); return "系统用户上传失败！"; } } } catch (Exception ex) { Logger.Error(ex); if (String.IsNullOrEmpty(logMessage)) return "系统用户上传失败！"; else { AddLastMessage(ref logMessage); return "系统用户上传失败！"; } } #endregion return "系统用户上传成功！"; } } #endregion #region 公司角色名称上传 //公司角色名称上传 public String UploadTsysCompRole(String filePath, String compId, String FileName) { String logInfo = ""; String logMessage = ""; Logger.Information("开始上传公司角色..."); var extion = Path.GetExtension(filePath); int sheetNum = 1;//读取sheet页 Guid Compid = Guid.Parse(compId); TsasRegCompany company = _unitOfWork.Repository<TsasRegCompany>().Get(x => x.Id == Compid).ToList().FirstOrDefault(); //类型,长度验证 using (FileStream fs = new FileStream(filePath, FileMode.Open)) { if (!this.BaseDataTemplateCheckTemplate(extion, fs, compId, FileName, ref logMessage, sheetNum)) { return "公司角色上传失败！"; } } #region 数据存在，不能再次插入 TsysUser User = new TsysUser(); try { User = _unitOfWork.Repository<TsysUser>().Get(x => x.CompanyId == Compid && x.AdminRole.Equals("087002") && x.Status == 0).ToList().FirstOrDefault(); if (User == null) { logInfo = "公司" + company.CompanyName.ToString() + "管理员用户不存在"; AddMessage(logInfo, ref logMessage); // AddLastMessage(ref logMessage); return "公司角色上传失败！"; } } catch (Exception ex) { Logger.Information(ex.Message); } Guid UserId = User.Id; Guid OrganizationId = User.OrgId.Value; List<TsysCompRole> CompRoleList = new List<TsysCompRole>(); try { CompRoleList = _unitOfWork.Repository<TsysCompRole>().Get(x => x.CompanyId == Compid && x.Status == 0).ToList(); } catch (Exception ex) { Logger.Information(ex.Message); } if (CompRoleList.Count > 3) { foreach (var item in CompRoleList) { if (item.Role_Name.Equals("全部角色") || item.Role_Name.Equals("普通用户") || item.Role_Name.Equals("系统管理员")) { } else { try { _tsysCompRoleBusiness.ISPDelete(item.Id); } catch (Exception ex) { item.Status = EntityStatusEnum.Active; _tsysCompRoleBusiness.ISPUpdate(item); logInfo = "数据正在被使用，无法再次重新上传，请在功能菜单上更正数据。"; AddMessage(logInfo, ref logMessage); // AddLastMessage(ref logMessage); return "公司角色上传失败！"; } } } } #endregion #region 共同验证 容器准备 //容器准备 List<Tuple<String, String, String, String>> AllExcelData = new List<Tuple<String, String, String, String>>(); Dictionary<int, TsysCompRole> AllFixedData = new Dictionary<int, TsysCompRole>(); List<CheckTreeBModel> AllTree = new List<CheckTreeBModel>(); List<UploadCompRoleBModel> NodeRoles = new List<UploadCompRoleBModel>(); List<CheckRoleTreeBModel> AllRoleTree = new List<CheckRoleTreeBModel>(); //用来存储excel和db中所有角色名称 #endregion #region 读取excel数据 using (FileStream newFs = new FileStream(filePath, FileMode.Open)) { IExcelDataReader excelReader; if (extion.Equals(".xls")) { excelReader = ExcelReaderFactory.CreateBinaryReader(newFs); } else { excelReader = ExcelReaderFactory.CreateOpenXmlReader(newFs); } Boolean AllCheckResult = true; var sheet = excelReader.AsDataSet().Tables[sheetNum]; for (int r = 3; r < sheet.Rows.Count; r++) { Object[] currentRow = sheet.Rows[r].ItemArray; #region 读取行数据 if (String.IsNullOrEmpty(currentRow[0].ToString()) && String.IsNullOrEmpty(currentRow[1].ToString()) && String.IsNullOrEmpty(currentRow[2].ToString()) && String.IsNullOrEmpty(currentRow[3].ToString()) ) { continue; } Tuple<String, String, String, String> ThisRow = new Tuple<String, String, String, String>( currentRow[0].ToString(), //角色编码 currentRow[1].ToString(), //角色名称 currentRow[2].ToString(), //角色类型 currentRow[3].ToString() //父角色编码 ); #endregion //编码重复验证在Excel AllExcelData.Add(ThisRow); AllTree.Add(new CheckTreeBModel() { Child = ThisRow.Item1, Parent = ThisRow.Item4, RowNumber = r + 1 }); AllRoleTree.Add(new CheckRoleTreeBModel() { Child = ThisRow.Item1, Parent = ThisRow.Item4, RowNumber = r + 1, Role_Type = ThisRow.Item3 }); } #endregion // 角色类型 var ProductiveNature = Guid.Parse("00000000-0000-0000-0000-000000000000"); List<TsysCompCode> Role_Type = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == ProductiveNature && t.Category_Code == "072" && t.Status == EntityStatusEnum.Active).ToList(); #region 父子级验证,码表验证 List<CheckTreeBModel> TreeBModel = new List<CheckTreeBModel>(); TreeBModel = _tsysManagementBusiness.CheckTree(AllTree); for (int i = 0; i < AllExcelData.Count; i++) { Boolean isTrue = _tsysManagementBusiness.CheckTree(TreeBModel, AllExcelData[i].Item1, AllExcelData[i].Item4); if (!isTrue) { logInfo = "第" + AllTree[i].RowNumber.ToString() + "行" + "角色编码父子级不正确"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } TsysCompCode _Role_Type = Role_Type.FirstOrDefault(t => t.Item_Name.Equals(AllExcelData[i].Item3)); //码表Check if (_Role_Type == null) { logInfo = "第" + AllTree[i].RowNumber.ToString() + "行" + (3) + "列" + "角色类型未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } //父元素角色类型Check if (AllRoleTree.Any(x => x.Child == AllExcelData[i].Item4) && !AllRoleTree.Any(x => x.Child == AllExcelData[i].Item4 && (x.Role_Type.Equals("分组") || x.Role_Type.Equals("072001")))) { logInfo = "第" + AllTree[i].RowNumber.ToString() + "行父元素角色类型为角色，不可新建子角色"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } } #endregion #region Check不过Return if (!AllCheckResult) { AddLastMessage(ref logMessage); return "公司角色上传失败！"; } #endregion #region Tree结构 Hashtable nodeRoleList = new Hashtable(); int row = 0; foreach (var item in AllExcelData) { TsysCompCode _Role_Type = Role_Type.FirstOrDefault(t => t.Item_Name.Equals(item.Item3)); nodeRoleList.Add(item.Item1, new UploadCompRoleBModel { ChildCode = item.Item1, Id = Guid.NewGuid(), ParentCode = item.Item4, RoleName = item.Item2, Role_Type = _Role_Type.Category_Code + _Role_Type.Item_Code, RowExcel = row++, Items = new List<UploadCompRoleBModel>() }); } Hashtable SortRoleList = new Hashtable(); foreach (UploadCompRoleBModel item in nodeRoleList.Values) { if (string.IsNullOrEmpty(item.ParentCode)) NodeRoles.Add(item); else { List<UploadCompRoleBModel> Items = ((UploadCompRoleBModel)nodeRoleList[item.ParentCode]).Items; Items.Add(item); if (SortRoleList[item.ParentCode] == null && Items.Count > 1) { SortRoleList.Add(item.ParentCode, Items); } } } #endregion AllFixedData = _tsysManagementBusiness.RoleBModelToEntity(NodeRoles, Compid);//获取父子级顺序的集合 #region Insert/Update ; // 获取一个空表 DataTable InsertDT = DBHelper.GetDataSet("select * from TsysCompRole where 1<>1"); AllFixedData.ToList().ForEach(t => { DataRow InsertRow = InsertDT.NewRow(); InsertRow["Id"] = t.Value.Id; InsertRow["Role_Code"] = String.IsNullOrEmpty(t.Value.Role_Code) ? "" : t.Value.Role_Code; InsertRow["Role_Name"] = String.IsNullOrEmpty(t.Value.Role_Name) ? "" : t.Value.Role_Name; InsertRow["Role_Type"] = String.IsNullOrEmpty(t.Value.Role_Type) ? "" : t.Value.Role_Type; if (t.Value.SortNo != null) InsertRow["SortNo"] = t.Value.SortNo; InsertRow["Node_Code"] = String.IsNullOrEmpty(t.Value.Node_Code) ? "" : t.Value.Node_Code; if (t.Value.Parent_RoleId != null) InsertRow["Parent_RoleId"] = t.Value.Parent_RoleId; InsertRow["OrganizationId"] = OrganizationId; InsertRow["UserId"] = UserId; InsertRow["CompanyId"] = Compid; InsertRow["SourceId"] = String.IsNullOrEmpty(t.Value.SourceId) ? "" : t.Value.SourceId; InsertRow["Status"] = 0; InsertRow["RowVersion"] = default(byte[]); InsertRow["CreatedDate"] = DateTime.Now; InsertRow["CreatedBy"] = UserId; InsertRow["UpdatedDate"] = DateTime.Now; InsertRow["UpdatedBy"] = UserId; InsertDT.Rows.Add(InsertRow); }); try { DBHelper.BulkInsert(InsertDT, "TsysCompRole"); } catch (Exception ex) { Logger.Error(ex); if (String.IsNullOrEmpty(logMessage)) return "公司角色上传失败！"; else { AddLastMessage(ref logMessage); return "公司角色上传失败！"; } } #endregion return "公司角色上传成功！"; } // } //} } #endregion #region 人员基本信息上传 // 人员基本信息上传 public String UploadTerpPersonInfo(String filePath, String compId, string FileName) { String logMessage = ""; String logInfo = ""; Logger.Information("开始上传人员基本信息..."); var extion = Path.GetExtension(filePath); int sheetNum = 1;//读取sheet页 #region ready using (FileStream fs = new FileStream(filePath, FileMode.Open)) { if (!this.BaseDataTemplateCheckTemplate(extion, fs, compId, FileName, ref logMessage, sheetNum)) { return "人员基本信息上传失败！"; } } #endregion #region get all value Guid compid = Guid.Parse(compId); TsasRegCompany company = _unitOfWork.Repository<TsasRegCompany>().Get(x => x.Id == compid).ToList().FirstOrDefault(); using (FileStream newFs = new FileStream(filePath, FileMode.Open)) { IExcelDataReader excelReader; if (extion.Equals(".xls")) { excelReader = ExcelReaderFactory.CreateBinaryReader(newFs); } else { excelReader = ExcelReaderFactory.CreateOpenXmlReader(newFs); } Boolean AllCheckResult = true; TsysUser User = new TsysUser(); try { User = _unitOfWork.Repository<TsysUser>().Get(x => x.CompanyId == compid && x.AdminRole.Equals("087002") && x.Status == 0).ToList().FirstOrDefault(); if (User == null) { logInfo = "公司" + company.CompanyName.ToString() + "管理员用户不存在"; if (!excelReader.IsClosed) { excelReader.Close(); } AddMessage(logInfo, ref logMessage); // AddLastMessage(ref logMessage); return "人员基本信息上传失败！"; } } catch (Exception ex) { Logger.Information(ex.Message); } Guid UserId = User.Id; Guid OrganizationId = User.OrgId.Value; #region 码表插入 System.Data.DataTable secondSheet = excelReader.AsDataSet().Tables[sheetNum + 1]; secondSheet.Rows[0].Delete(); secondSheet.AcceptChanges(); //政治状态 SaveCompanyCodeCategoryBySecondSheet(0, secondSheet, "006", compid); //民族 SaveCompanyCodeCategoryBySecondSheet(1, secondSheet, "190", compid); //在职状态 SaveCompanyCodeCategoryBySecondSheet(2, secondSheet, "009", compid); //职称 SaveCompanyCodeCategoryBySecondSheet(3, secondSheet, "007", compid); //银行 SaveCompanyCodeCategoryBySecondSheet(4, secondSheet, "004", compid); //离职类型 SaveCompanyCodeCategoryBySecondSheet(5, secondSheet, "010", compid); //户口性质 SaveCompanyCodeCategoryBySecondSheet(6, secondSheet, "213", compid); //所属公司 SaveCompanyCodeCategoryBySecondSheet(7, secondSheet, "214", compid); #endregion List<TerpPersonInfo> AllFixdExeclData = new List<TerpPersonInfo>(); List<TsysCompOrg> allOrg = _tsysManagementBusiness.GetAllSYSCompOrg(compId); // 性别 var ProductiveNature = Guid.Parse("00000000-0000-0000-0000-000000000000"); List<TsysCompCode> gender = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == ProductiveNature && t.Category_Code == "001" && t.Status == EntityStatusEnum.Active).ToList(); // 婚姻状况 ProductiveNature = Guid.Parse("00000000-0000-0000-0000-000000000000"); List<TsysCompCode> maritalStatus = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == ProductiveNature && t.Category_Code == "003" && t.Status == EntityStatusEnum.Active).ToList(); // 政治面貌 List<TsysCompCode> PoliticsStatus = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "006" && t.Status == EntityStatusEnum.Active).ToList(); // 民族 List<TsysCompCode> Nationality = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "190" && t.Status == EntityStatusEnum.Active).ToList(); // 在职状态 List<TsysCompCode> workState = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "009" && t.Status == EntityStatusEnum.Active).ToList(); // 员工类型 ProductiveNature = Guid.Parse("00000000-0000-0000-0000-000000000000"); List<TsysCompCode> employeeType = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == ProductiveNature && t.Category_Code == "197" && t.Status == EntityStatusEnum.Active).ToList(); // 最高职称 List<TsysCompCode> workRank = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "007" && t.Status == EntityStatusEnum.Active).ToList(); // 开户银行 List<TsysCompCode> Bank = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "004" && t.Status == EntityStatusEnum.Active).ToList(); // 离职类型 List<TsysCompCode> dimissionType = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "010" && t.Status == EntityStatusEnum.Active).ToList(); //户口性质 List<TsysCompCode> Nature = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "213" && t.Status == EntityStatusEnum.Active).ToList(); //所属公司 List<TsysCompCode> CompanyName = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "214" && t.Status == EntityStatusEnum.Active).ToList(); //读取数据 var sheet = excelReader.AsDataSet().Tables[sheetNum]; for (int r = 3; r < sheet.Rows.Count; r++) { Object[] currentRow = sheet.Rows[r].ItemArray; if ( String.IsNullOrEmpty(currentRow[0].ToString()) && String.IsNullOrEmpty(currentRow[1].ToString()) && String.IsNullOrEmpty(currentRow[2].ToString()) && String.IsNullOrEmpty(currentRow[3].ToString()) && String.IsNullOrEmpty(currentRow[4].ToString()) && String.IsNullOrEmpty(currentRow[5].ToString()) && String.IsNullOrEmpty(currentRow[6].ToString()) && String.IsNullOrEmpty(currentRow[7].ToString()) && String.IsNullOrEmpty(currentRow[8].ToString()) && String.IsNullOrEmpty(currentRow[9].ToString()) && String.IsNullOrEmpty(currentRow[10].ToString()) && String.IsNullOrEmpty(currentRow[11].ToString()) && String.IsNullOrEmpty(currentRow[12].ToString()) && String.IsNullOrEmpty(currentRow[13].ToString()) && String.IsNullOrEmpty(currentRow[14].ToString()) && String.IsNullOrEmpty(currentRow[15].ToString()) && String.IsNullOrEmpty(currentRow[16].ToString()) && String.IsNullOrEmpty(currentRow[17].ToString()) && String.IsNullOrEmpty(currentRow[18].ToString()) && String.IsNullOrEmpty(currentRow[19].ToString()) && String.IsNullOrEmpty(currentRow[20].ToString()) && String.IsNullOrEmpty(currentRow[21].ToString()) && String.IsNullOrEmpty(currentRow[22].ToString()) && String.IsNullOrEmpty(currentRow[23].ToString()) && String.IsNullOrEmpty(currentRow[24].ToString()) && String.IsNullOrEmpty(currentRow[25].ToString()) && String.IsNullOrEmpty(currentRow[26].ToString()) && String.IsNullOrEmpty(currentRow[27].ToString()) && String.IsNullOrEmpty(currentRow[28].ToString()) && String.IsNullOrEmpty(currentRow[29].ToString()) && String.IsNullOrEmpty(currentRow[30].ToString()) && String.IsNullOrEmpty(currentRow[31].ToString()) && String.IsNullOrEmpty(currentRow[32].ToString()) && String.IsNullOrEmpty(currentRow[33].ToString()) && String.IsNullOrEmpty(currentRow[34].ToString()) ) { continue; } //员工编号重复验证在Excel TerpPersonInfo person = new TerpPersonInfo(); person.Id = Guid.NewGuid(); person.EmployeeCode = currentRow[0].ToString();//员工编号 person.EmployeeName = currentRow[1].ToString();//姓名 string orgName = currentRow[2].ToString();//所属组织 if (allOrg == null || allOrg.Count == 0) { logInfo = "公司组织机构信息未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { TsysCompOrg org = allOrg.Where(x => !String.IsNullOrEmpty(x.OrgName) && x.OrgName.Equals(orgName)).FirstOrDefault(); if (org == null) { logInfo = "第" + (r + 1).ToString() + "行第3列所属组织未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.OrgId = org.Id; } } TsysCompCode _gender = gender.FirstOrDefault(t => t.Item_Name.Equals(currentRow[3].ToString())); //性别 码表 if (_gender == null) { logInfo = "第" + (r + 1).ToString() + "行第4列性别未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.Gender = _gender.Category_Code + _gender.Item_Code; } if (!String.IsNullOrEmpty(currentRow[4].ToString())) { person.Birthdate = Convert.ToDateTime(currentRow[4].ToString());//出生日期 } if (!string.IsNullOrEmpty(currentRow[5].ToString())) { TsysCompCode _maritalStatus = maritalStatus.FirstOrDefault(t => t.Item_Name.Equals(currentRow[5].ToString()));//婚姻状况 码表 if (_maritalStatus == null) { logInfo = "第" + (r + 1).ToString() + "行第6列婚姻状况未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.MaritalStatus = _maritalStatus.Category_Code + _maritalStatus.Item_Code; } } person.IdNumber = currentRow[6].ToString();//身份证号 if (!String.IsNullOrEmpty(currentRow[7].ToString())) { TsysCompCode _PoliticsStatus = PoliticsStatus.FirstOrDefault(t => t.Item_Name.Equals(currentRow[7].ToString()));//政治面貌 码表 if (_PoliticsStatus == null) { logInfo = "第" + (r + 1).ToString() + "行第8列政治面貌未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.PoliticsStatus = _PoliticsStatus.Category_Code + _PoliticsStatus.Item_Code; } } if (!String.IsNullOrEmpty(currentRow[8].ToString())) { TsysCompCode _Nationality = Nationality.FirstOrDefault(t => t.Item_Name.Equals(currentRow[8].ToString())); //民族 码表 if (_Nationality == null) { logInfo = "第" + (r + 1).ToString() + "行第9列民族未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.Nationality = _Nationality.Category_Code + _Nationality.Item_Code; } } person.MobilePhone = currentRow[9].ToString();//移动电话 person.InterPhone = currentRow[10].ToString();//内线电话 person.GroupPhone = currentRow[11].ToString();//办公电话 person.EmailAddress = currentRow[12].ToString();//电子邮件 person.EmergencyContact = currentRow[13].ToString();//紧急联系人 person.EmergencyTel = currentRow[14].ToString();//紧急联系人电话 person.HomePhone = currentRow[15].ToString();//家庭电话 person.HomeAddress = currentRow[16].ToString();//家庭住址 person.Memo = currentRow[17].ToString();//备注 if (!String.IsNullOrEmpty(currentRow[18].ToString())) { TsysCompCode _workState = workState.FirstOrDefault(t => t.Item_Name.Equals(currentRow[18].ToString()));//在职状态 码表 if (_workState == null) { logInfo = "第" + (r + 1).ToString() + "行第19列在职状态未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.WorkState = _workState.Category_Code + _workState.Item_Code; } } if (!String.IsNullOrEmpty(currentRow[19].ToString())) { TsysCompCode _employeeType = employeeType.FirstOrDefault(t => t.Item_Name.Equals(currentRow[19].ToString())); //员工类型 码表 if (_employeeType == null) { logInfo = "第" + (r + 1).ToString() + "行第20列员工类型未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.employeeType = _employeeType.Category_Code + _employeeType.Item_Code; } } //参加工作日 if (!String.IsNullOrEmpty(currentRow[20].ToString())) { person.WorkStartDate = Convert.ToDateTime(currentRow[20].ToString()); } //职务/岗位 person.Duty = currentRow[21].ToString(); //加入工作日 if (!String.IsNullOrEmpty(currentRow[22].ToString())) { person.TrialPeriodStartDate = Convert.ToDateTime(currentRow[22].ToString()); } //转正日期 if (!String.IsNullOrEmpty(currentRow[23].ToString())) { person.RegularStartDate = Convert.ToDateTime(currentRow[23].ToString()); } //最高职称 if (!String.IsNullOrEmpty(currentRow[24].ToString())) { TsysCompCode _workRank = workRank.FirstOrDefault(t => t.Item_Name.Equals(currentRow[24].ToString()));//最高职称 码表 if (_workRank == null) { logInfo = "第" + (r + 1).ToString() + "行第25列最高职称未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.WorkRank = _workRank.Category_Code + _workRank.Item_Code; } } //开户银行 if (!String.IsNullOrEmpty(currentRow[25].ToString())) { TsysCompCode _Bank = Bank.FirstOrDefault(t => t.Item_Name.Equals(currentRow[25].ToString()));//开户银行 码表 if (_Bank == null) { logInfo = "第" + (r + 1).ToString() + "行第26列开户银行未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.Bank = _Bank.Category_Code + _Bank.Item_Code; } } person.BankCardNo = currentRow[26].ToString();//银行账号 //离职日期 if (!String.IsNullOrEmpty(currentRow[27].ToString())) { person.DimissionDate = Convert.ToDateTime(currentRow[27].ToString()); } //离职类型 if (!String.IsNullOrEmpty(currentRow[28].ToString())) { TsysCompCode _dimissionType = dimissionType.FirstOrDefault(t => t.Item_Name.Equals(currentRow[28].ToString())); if (_dimissionType == null) { logInfo = "第" + (r + 1).ToString() + "行第29列离职类型未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.DimissionType = _dimissionType.Category_Code + _dimissionType.Item_Code; } } //离职原因 person.DimissionReason = currentRow[29].ToString(); //户籍所在地 person.Domicile = currentRow[30].ToString(); //参保时间 if (!String.IsNullOrEmpty(currentRow[31].ToString())) { person.InsuredTime = Convert.ToDateTime(currentRow[31].ToString()); } //户口性质 if (!String.IsNullOrEmpty(currentRow[32].ToString())) { TsysCompCode _Nature = Nature.FirstOrDefault(t => t.Item_Name.Equals(currentRow[32].ToString())); if (_Nature == null) { logInfo = "第" + (r + 1).ToString() + "行第33列离职类型未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.Nature = _Nature.Category_Code + _Nature.Item_Code; } } //家庭成员 person.Memberoffamily = currentRow[33].ToString(); //所属公司 if (!String.IsNullOrEmpty(currentRow[34].ToString())) { TsysCompCode _CompanyName = CompanyName.FirstOrDefault(t => t.Item_Name.Equals(currentRow[34].ToString())); if (_CompanyName == null) { logInfo = "第" + (r + 1).ToString() + "行第35列离职类型未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { person.CompanyName = _CompanyName.Category_Code + _CompanyName.Item_Code; } } AllFixdExeclData.Add(person); } if (!AllCheckResult) { AddLastMessage(ref logMessage); return "人员基本信息上传失败！"; } #endregion #region insert / update //取出当前表中数据 List<TerpPersonInfo> _list = _unitOfWork.Repository<TerpPersonInfo>().Get(t => t.Status == EntityStatusEnum.Active && t.CompanyId == compid).ToList(); // 左联表，若当前DB中无匹配的数据则t2返回空，即t2 为空的是DB中不存在的数据，直接Insert（建议临时库可不考虑Update 情况，直接清表后Insert）， var mearge = from a in AllFixdExeclData join b in _list on a.EmployeeCode equals b.EmployeeCode into temp from tt in temp.DefaultIfEmpty() select new { t1 = a, t2 = tt }; // 取出需要Insert的记录 var batchInsert = mearge.Where(t => t.t2 == null).Select(t => t.t1).ToList(); var batchOldUpdate = mearge.Where(t => t.t2 != null).Select(t => t.t2).ToList(); var batchUpdate = mearge.Where(t => t.t2 != null).Select(t => t.t1).ToList(); ; // 获取一个空表 DataTable PersonDT = DBHelper.GetDataSet("select * from TerpPersonInfo where 1<>1"); DataTable UserDT = DBHelper.GetDataSet("select * from TsysUser where 1<>1"); DataTable UserRoleDT = DBHelper.GetDataSet("select * from TsysUserRole where 1<>1"); String passWord = "DC3360"; Guid RelativeRoleId = _unitOfWork.Repository<TsysCompRole>().Get(x => x.Role_Name.Equals("普通用户") && x.CompanyId == compid).FirstOrDefault().Id; batchInsert.ForEach(t => { DataRow personRow = PersonDT.NewRow(); personRow["Id"] = t.Id; personRow["EmployeeCode"] = String.IsNullOrEmpty(t.EmployeeCode) ? "" : t.EmployeeCode; personRow["EmployeeName"] = String.IsNullOrEmpty(t.EmployeeName) ? "" : t.EmployeeName; personRow["Gender"] = String.IsNullOrEmpty(t.Gender) ? "" : t.Gender; personRow["Nationality"] = String.IsNullOrEmpty(t.Nationality) ? "" : t.Nationality; personRow["EmailAddress"] = String.IsNullOrEmpty(t.EmailAddress) ? "" : t.EmailAddress; personRow["IdNumber"] = String.IsNullOrEmpty(t.IdNumber) ? "" : t.IdNumber; personRow["Nation"] = String.IsNullOrEmpty(t.Nation) ? "" : t.Nation; personRow["HomeAddress"] = String.IsNullOrEmpty(t.HomeAddress) ? "" : t.HomeAddress; personRow["PostCode"] = String.IsNullOrEmpty(t.PostCode) ? "" : t.PostCode; personRow["MaritalStatus"] = String.IsNullOrEmpty(t.MaritalStatus) ? "" : t.MaritalStatus; personRow["Country"] = String.IsNullOrEmpty(t.Country) ? "" : t.Country; personRow["Birthplace"] = String.IsNullOrEmpty(t.Birthplace) ? "" : t.Birthplace; personRow["PoliticsStatus"] = String.IsNullOrEmpty(t.PoliticsStatus) ? "" : t.PoliticsStatus; personRow["EmergencyContact"] = String.IsNullOrEmpty(t.EmergencyContact) ? "" : t.EmergencyContact; personRow["EmergencyTel"] = String.IsNullOrEmpty(t.EmergencyTel) ? "" : t.EmergencyTel; if (t.Birthdate != null) personRow["Birthdate"] = t.Birthdate; if (t.OrgId != null) personRow["OrgId"] = t.OrgId; personRow["Duty"] = String.IsNullOrEmpty(t.Duty) ? "" : t.Duty; personRow["MobilePhone"] = String.IsNullOrEmpty(t.MobilePhone) ? "" : t.MobilePhone; personRow["HomePhone"] = String.IsNullOrEmpty(t.HomePhone) ? "" : t.HomePhone; personRow["GraduateSchool"] = String.IsNullOrEmpty(t.GraduateSchool) ? "" : t.GraduateSchool; personRow["Major"] = String.IsNullOrEmpty(t.Major) ? "" : t.Major; personRow["Education"] = String.IsNullOrEmpty(t.Education) ? "" : t.Education; personRow["Degree"] = String.IsNullOrEmpty(t.Degree) ? "" : t.Degree; if (t.GraduateDate != null) personRow["GraduateDate"] = t.GraduateDate; if (t.WorkStartDate != null) personRow["WorkStartDate"] = t.WorkStartDate; personRow["TrialPeriod"] = String.IsNullOrEmpty(t.TrialPeriod) ? "" : t.TrialPeriod; if (t.TrialPeriodStartDate != null) personRow["TrialPeriodStartDate"] = t.TrialPeriodStartDate; if (t.TrialPeriodEndDate != null) personRow["TrialPeriodEndDate"] = t.TrialPeriodEndDate; personRow["RegularType"] = String.IsNullOrEmpty(t.RegularType) ? "" : t.RegularType; if (t.RegularStartDate != null) personRow["RegularStartDate"] = t.RegularStartDate; personRow["CertificateFlg"] = String.IsNullOrEmpty(t.CertificateFlg) ? "" : t.CertificateFlg; personRow["Bank"] = String.IsNullOrEmpty(t.Bank) ? "" : t.Bank; personRow["BankCardNo"] = String.IsNullOrEmpty(t.BankCardNo) ? "" : t.BankCardNo; personRow["GroupPhone"] = String.IsNullOrEmpty(t.GroupPhone) ? "" : t.GroupPhone; personRow["InterPhone"] = String.IsNullOrEmpty(t.InterPhone) ? "" : t.InterPhone; personRow["WorkState"] = String.IsNullOrEmpty(t.WorkState) ? "" : t.WorkState; personRow["DimissionType"] = String.IsNullOrEmpty(t.DimissionType) ? "" : t.DimissionType; if (t.DimissionDate != null) personRow["DimissionDate"] = t.DimissionDate; personRow["DimissionReason"] = String.IsNullOrEmpty(t.DimissionReason) ? "" : t.DimissionReason; personRow["ArchivalPlace"] = String.IsNullOrEmpty(t.ArchivalPlace) ? "" : t.ArchivalPlace; if (t.EntryDate != null) personRow["EntryDate"] = t.EntryDate; personRow["WorkRank"] = String.IsNullOrEmpty(t.WorkRank) ? "" : t.WorkRank; //personRow["EmployeePic"] = String.IsNullOrEmpty(t.EmployeePic.ToString()) ? "" : t.EmployeePic.ToString(); personRow["Memo"] = String.IsNullOrEmpty(t.Memo) ? "" : t.Memo; personRow["Domicile"] = String.IsNullOrEmpty(t.Domicile) ? "" : t.Domicile; if (t.InsuredTime != null) personRow["InsuredTime"] = t.InsuredTime; personRow["Nature"] = String.IsNullOrEmpty(t.Nature) ? "" : t.Nature; personRow["Memberoffamily"] = String.IsNullOrEmpty(t.Memberoffamily) ? "" : t.Memberoffamily; personRow["CompanyName"] = String.IsNullOrEmpty(t.CompanyName) ? "" : t.CompanyName; personRow["employeeType"] = String.IsNullOrEmpty(t.employeeType) ? "" : t.employeeType; personRow["OrganizationId"] = OrganizationId; personRow["UserId"] = UserId; personRow["CompanyId"] = compid; personRow["SourceId"] = String.IsNullOrEmpty(t.SourceId) ? "" : t.SourceId; personRow["Status"] = 0; personRow["RowVersion"] = default(byte[]); personRow["CreatedDate"] = DateTime.Now; personRow["CreatedBy"] = UserId; personRow["UpdatedDate"] = DateTime.Now; personRow["UpdatedBy"] = UserId; PersonDT.Rows.Add(personRow); DataRow userRow = UserDT.NewRow(); userRow["Id"] = t.Id; if (!String.IsNullOrEmpty(t.EmployeeCode) && !String.IsNullOrEmpty(company.CompanyLoginMark)) userRow["LoginId"] = t.EmployeeCode + "@" + company.CompanyLoginMark; ; String salt = CreateSalt(); userRow["LoginPwd"] = ComputeHash(passWord, salt); userRow["Salt"] = String.IsNullOrEmpty(salt) ? "" : salt; userRow["AdminRole"] = "087003"; userRow["UserName"] = String.IsNullOrEmpty(t.EmployeeName) ? "" : t.EmployeeName; userRow["Gender"] = String.IsNullOrEmpty(t.Gender) ? "" : t.Gender; userRow["EmployeeCode"] = String.IsNullOrEmpty(t.EmployeeCode) ? "" : t.EmployeeCode; userRow["OrgId"] = t.OrgId; userRow["UserEmail"] = String.IsNullOrEmpty(t.EmailAddress) ? "" : t.EmailAddress; userRow["UserTel"] = String.IsNullOrEmpty(t.MobilePhone) ? "" : t.MobilePhone; //userRow["VerificationCode"] = String.IsNullOrEmpty(t.VerificationCode) ? "" : t.VerificationCode; //if (t.VerificationDate != null) userRow["VerificationDate"] = t.VerificationDate; userRow["Memo"] = String.IsNullOrEmpty(t.Memo) ? "" : t.Memo; userRow["BelongToCompanyId"] = compid; userRow["OrganizationId"] = OrganizationId; userRow["UserId"] = UserId; userRow["CompanyId"] = compid; userRow["SourceId"] = String.IsNullOrEmpty(t.SourceId) ? "" : t.SourceId; userRow["Status"] = 0; userRow["RowVersion"] = default(byte[]); userRow["CreatedDate"] = DateTime.Now; userRow["CreatedBy"] = UserId; userRow["UpdatedDate"] = DateTime.Now; userRow["UpdatedBy"] = UserId; userRow["Enable"] = "036002"; UserDT.Rows.Add(userRow); DataRow userRoleRow = UserRoleDT.NewRow(); userRoleRow["Id"] = Guid.NewGuid(); if (t.Id != null) userRoleRow["RelativeUserId"] = t.Id; if (RelativeRoleId != null) userRoleRow["RelativeRoleId"] = RelativeRoleId; userRoleRow["OrganizationId"] = OrganizationId; userRoleRow["UserId"] = UserId; userRoleRow["CompanyId"] = compid; userRoleRow["SourceId"] = String.IsNullOrEmpty(t.SourceId) ? "" : t.SourceId; userRoleRow["Status"] = 0; userRoleRow["RowVersion"] = default(byte[]); userRoleRow["CreatedDate"] = DateTime.Now; userRoleRow["CreatedBy"] = UserId; userRoleRow["UpdatedDate"] = DateTime.Now; userRoleRow["UpdatedBy"] = UserId; UserRoleDT.Rows.Add(userRoleRow); }); try { DBHelper.BulkInsert(PersonDT, "TerpPersonInfo"); DBHelper.BulkInsert(UserDT, "TsysUser"); DBHelper.BulkInsert(UserRoleDT, "TsysUserRole"); } catch (Exception ex) { Logger.Error(ex); if (String.IsNullOrEmpty(logMessage)) return "人员基本信息上传失败！"; else { AddLastMessage(ref logMessage); return "人员基本信息上传失败！"; } } try { using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 20, 0))) { var dbset = _context.Set<TerpPersonInfo>().ToList(); var dbsetUser = _context.Set<TsysUser>().ToList(); for (int i = 0; i < batchOldUpdate.Count; i++) { TerpPersonInfo findReuslt = dbset.FirstOrDefault(t => t.Id.Equals(batchOldUpdate[i].Id)); TerpPersonInfo updateResult = batchUpdate.FirstOrDefault(t => t.EmployeeCode.Equals(batchOldUpdate[i].EmployeeCode)); findReuslt.EmployeeCode = updateResult.EmployeeCode; findReuslt.EmployeeName = updateResult.EmployeeName; findReuslt.OrgId = updateResult.OrgId; findReuslt.Gender = updateResult.Gender; findReuslt.Birthdate = Convert.ToDateTime(updateResult.Birthdate); findReuslt.MaritalStatus = updateResult.MaritalStatus; findReuslt.IdNumber = updateResult.IdNumber; findReuslt.PoliticsStatus = updateResult.PoliticsStatus; findReuslt.Nationality = updateResult.Nationality; findReuslt.MobilePhone = updateResult.MobilePhone; findReuslt.InterPhone = updateResult.InterPhone; findReuslt.GroupPhone = updateResult.GroupPhone; findReuslt.EmailAddress = updateResult.EmailAddress; findReuslt.EmergencyContact = updateResult.EmergencyContact; findReuslt.EmergencyTel = updateResult.EmergencyTel; findReuslt.HomePhone = updateResult.HomePhone; findReuslt.HomeAddress = updateResult.HomeAddress; findReuslt.Memo = updateResult.Memo; findReuslt.WorkState = updateResult.WorkState; findReuslt.employeeType = updateResult.employeeType; findReuslt.WorkStartDate = Convert.ToDateTime(updateResult.WorkStartDate); findReuslt.Duty = updateResult.Duty; findReuslt.TrialPeriodStartDate = Convert.ToDateTime(updateResult.TrialPeriodStartDate); findReuslt.RegularStartDate = Convert.ToDateTime(updateResult.RegularStartDate); findReuslt.WorkRank = updateResult.WorkRank; findReuslt.Bank = updateResult.Bank; findReuslt.BankCardNo = updateResult.BankCardNo; findReuslt.DimissionType = updateResult.DimissionType; findReuslt.DimissionDate = Convert.ToDateTime(updateResult.DimissionDate); findReuslt.DimissionReason = updateResult.DimissionReason; findReuslt.Domicile = updateResult.Domicile; findReuslt.InsuredTime = updateResult.InsuredTime; findReuslt.Nature = updateResult.Nature; findReuslt.Memberoffamily = updateResult.Memberoffamily; findReuslt.CompanyName = updateResult.CompanyName; findReuslt.UpdatedDate = DateTime.Now; TsysUser findUser = dbsetUser.FirstOrDefault(t => t.Id.Equals(batchOldUpdate[i].Id)); findUser.UserName = updateResult.EmployeeName; findUser.Gender = updateResult.Gender; findUser.OrgId = updateResult.OrgId; findUser.EmployeeCode = updateResult.EmployeeCode; findUser.UserEmail = updateResult.EmailAddress; findUser.UserTel = updateResult.MobilePhone; findUser.Memo = updateResult.Memo; findUser.UpdatedDate = DateTime.Now; } _context.SaveChanges(); scope.Complete(); } } catch (Exception ex) { Logger.Error(ex); if (String.IsNullOrEmpty(logMessage)) return "人员基本信息上传失败！"; else { AddLastMessage(ref logMessage); return "人员基本信息上传失败！"; } } #endregion return "人员基本信息上传成功！"; } } #endregion #region 用户角色设置上传 //用户角色设置上传 public String UploadTsysUserRole(String filePath, String compId, string FileName) { String logInfo = ""; String logMessage = ""; Logger.Information("开始上传用户角色设置..."); var extion = Path.GetExtension(filePath); Guid compid = Guid.Parse(compId); TsasRegCompany company = _unitOfWork.Repository<TsasRegCompany>().Get(x => x.Id == compid).ToList().FirstOrDefault(); #region 共通验证 容器准备 //数据类型和长度验证（共通） int sheetNum = 1;//读取sheet页 using (FileStream fs = new FileStream(filePath, FileMode.Open)) { if (!this.BaseDataTemplateCheckTemplate(extion, fs, compId, FileName, ref logMessage, sheetNum)) { return "用户角色设置上传失败！"; } } //定义容器 List<Tuple<String, String>> AllExcelData = new List<Tuple<String, String>>(); List<TsysUserRole> AllFixdExeclData = new List<TsysUserRole>(); List<Int32> LogRowNumber = new List<Int32>(); TsysUser User = new TsysUser(); try { User = _unitOfWork.Repository<TsysUser>().Get(x => x.CompanyId == compid && x.AdminRole.Equals("087002") && x.Status == 0).ToList().FirstOrDefault(); if (User == null) { logInfo = "公司" + company.CompanyName.ToString() + "管理员用户不存在"; AddMessage(logInfo, ref logMessage); // AddLastMessage(ref logMessage); return "用户角色设置上传失败！"; } } catch (Exception ex) { Logger.Information(ex.Message); } Guid UserId = User.Id; Guid OrganizationId = User.OrgId.Value; #endregion #region 获取Execl数据 using (FileStream newFs = new FileStream(filePath, FileMode.Open)) { IExcelDataReader excelReader; if (extion.Equals(".xls")) { excelReader = ExcelReaderFactory.CreateBinaryReader(newFs); } else { excelReader = ExcelReaderFactory.CreateOpenXmlReader(newFs); } Boolean AllCheckResult = true; //检查结果定义（所有Check） var sheet = excelReader.AsDataSet().Tables[sheetNum]; for (int r = 3; r < sheet.Rows.Count; r++) { Object[] currentRow = sheet.Rows[r].ItemArray; #region 获取行数据 //读行结束，跳出 if (String.IsNullOrEmpty(currentRow[0].ToString()) && String.IsNullOrEmpty(currentRow[1].ToString())) { continue; } //读取一行客户数据 Tuple<String, String> ThisRow = new Tuple<String, String>( currentRow[0].ToString(), currentRow[1].ToString() ); #endregion #region 重复性验证 Boolean RepeatCheckResult = true; //重复性验证 foreach (var item in AllExcelData) { if (item.Item1.Equals(ThisRow.Item1) && item.Item2 == null) { if (ThisRow.Item2 == null) { String message = "第" + (r + 1) + "行" + "该员工编号下角色编码重复"; AddMessage(message, ref logMessage); RepeatCheckResult = false; AllCheckResult = false; } } else { if (item.Item1.Equals(ThisRow.Item1) && item.Item2.Equals(ThisRow.Item2)) //员工编号和角色编码重复性验证 { String message = "第" + (r + 1) + "行" + "该员工编号下角色编码重复"; AddMessage(message, ref logMessage); RepeatCheckResult = false; AllCheckResult = false; } } } #endregion if (RepeatCheckResult) //验证通过 存储一条 { AllExcelData.Add(ThisRow); LogRowNumber.Add(r + 1); } } #endregion #region 外键来源 List<TsysUser> AllUser = _tsysManagementBusiness.GetAllTsysUser(compId).Where(x => x.Status == EntityStatusEnum.Active).ToList(); List<TsysCompRole> AllCompRole = _tsysManagementBusiness.GetTsysCompRole(compId).Where(x => x.Status == EntityStatusEnum.Active).ToList(); #endregion #region 插入数据准备 for (int i = 0; i < AllExcelData.Count; i++) { TsysUserRole UserRole = new TsysUserRole(); UserRole.Id = Guid.NewGuid(); UserRole.SourceId = AllExcelData[i].Item1; if (AllUser != null) { TsysUser UserN = AllUser.Where(x => !String.IsNullOrEmpty(x.EmployeeCode) && x.EmployeeCode.Equals(AllExcelData[i].Item1)).FirstOrDefault(); if (UserN == null) { logInfo = "第" + LogRowNumber[i].ToString() + "行第(1)列员工编号未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { UserRole.RelativeUserId = UserN.Id; } } else { logInfo = "第" + LogRowNumber[i].ToString() + "行第 (1) 列员工编号未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } if ((AllExcelData[i].Item2) == null) { TsysCompRole CompRoleN = AllCompRole.Where(x => !String.IsNullOrEmpty(x.Role_Name) && x.Role_Name.Equals("普通用户")).FirstOrDefault(); UserRole.RelativeRoleId = CompRoleN.Id; } else { TsysCompRole CompRoleN = AllCompRole.Where(x => !String.IsNullOrEmpty(x.Role_Code) && x.Role_Code.Equals(AllExcelData[i].Item2)).FirstOrDefault(); if (CompRoleN == null) { logInfo = "第" + LogRowNumber[i] + "行第 (2)列角色编码未获取到"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } else { UserRole.RelativeRoleId = CompRoleN.Id; } } //角色类型 为分组时不能添加人员 if (!string.IsNullOrEmpty(AllExcelData[i].Item2) && AllCompRole.Any(x => x.Role_Code == AllExcelData[i].Item2 && x.Role_Type == SysCode.角色类型.分组)) { logInfo = "第" + LogRowNumber[i] + "行第 (2)列角色类型为分组不能添加人员"; AddMessage(logInfo, ref logMessage); AllCheckResult = false; } if (!String.IsNullOrEmpty(compId)) { UserRole.CompanyId = Guid.Parse(compId); } AllFixdExeclData.Add(UserRole); } if (!AllCheckResult) { AddLastMessage(ref logMessage); return "用户角色设置上传失败！"; } #endregion #region Insert/Update //取出当前表中数据 List<TsysUserRole> _list = _unitOfWork.Repository<TsysUserRole>().Get(t => t.Status == EntityStatusEnum.Active && t.CompanyId == compid).ToList(); // 左联表，若当前DB中无匹配的数据则t2返回空，即t2 为空的是DB中不存在的数据，直接Insert（建议临时库可不考虑Update 情况，直接清表后Insert）， var mearge = from a in AllFixdExeclData join b in _list on a.RelativeUserId equals b.RelativeUserId into temp from tt in temp.DefaultIfEmpty() select new { t1 = a, t2 = tt }; // 取出需要Insert的记录 var batchInsert = mearge.Where(t => t.t2 == null).Select(t => t.t1).ToList(); ; // 获取一个空表 DataTable InsertDT = DBHelper.GetDataSet("select * from TsysUserRole where 1<>1"); batchInsert.ForEach(t => { DataRow row = InsertDT.NewRow(); row["Id"] = t.Id; row["RelativeUserId"] = String.IsNullOrEmpty(t.RelativeUserId.ToString()) ? "" : t.RelativeUserId.ToString(); row["RelativeRoleId"] = String.IsNullOrEmpty(t.RelativeRoleId.ToString()) ? "" : t.RelativeRoleId.ToString(); row["OrganizationId"] = OrganizationId; row["UserId"] = UserId; row["CompanyId"] = compid; row["SourceId"] = String.IsNullOrEmpty(t.SourceId) ? "" : t.SourceId; row["Status"] = 0; row["RowVersion"] = default(byte[]); row["CreatedDate"] = DateTime.Now; row["CreatedBy"] = UserId; row["UpdatedDate"] = DateTime.Now; row["UpdatedBy"] = UserId; InsertDT.Rows.Add(); }); #endregion return "用户角色设置上传成功！"; } } #endregion private Type GetTypeByDBType(String dbType) { switch (dbType) { case "nvarchar": case "varchar": case "char": case "nchar": return typeof(String); case "uniqueidentifier": return typeof(Guid); case "datetime": case "date": case "timestamp": return typeof(DateTime); case "smallint": return typeof(Int16); case "int": return typeof(Int32); case "bigint": return typeof(Int64); case "decimal": return typeof(Decimal); case "float": return typeof(float); default: return typeof(Object); } } private static string CreateSalt() { var data = new byte[128]; new RNGCryptoServiceProvider().GetBytes(data); return Convert.ToBase64String(data); } private static string ComputeHash(string source, string salt) { var bytes = Encoding.Unicode.GetBytes(salt.ToLower() + source); return ComputeHash(bytes); } private static String ComputeHash(byte[] buffer) { using (var hashProvider = new SHA1CryptoServiceProvider()) { var sha1Hash = hashProvider.ComputeHash(buffer); return Convert.ToBase64String(sha1Hash); } } public void SaveCompanyCodeCategoryBySecondSheet(int index, DataTable secondSheet, string categorycode, Guid compid) { var collection = secondSheet.AsEnumerable().Select(row => row[index].ToString()); foreach (var codename in collection) { if (!string.IsNullOrEmpty(codename)) { string stringResult = _tsysManagementBusiness.GetCodeByItemNameAndCategoryCode(codename, categorycode, compid); if (string.IsNullOrEmpty(stringResult)) { _tsysCompCodeBusiness.SaveCompanyCodeCategoryByCompId(codename, categorycode, compid); } } } } private void AddMessage(string message, ref string logMessage) { RDS.Data += message + "</BR>"; } private void AddLastMessage(ref string logMessage) { RDS.Data += logMessage + "</BR>"; } } }
版权声明：本文为daimaxuejia原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。