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(, FilePath.Split('\\').Last().IndexOf('+'));
var FileName = fileName.Substring(, fileName.Length - );
// 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("成功") > -)
{
RDS.Status = ;
RDS.Data = string.Empty;
}
return RDS;
} #region 数据类型和长度验证（模板用 可sheet页选读）
public Boolean BaseDataTemplateCheckTemplate(String extion, Stream fs, string compId, string FileName, ref String logMessage, int sheetIndex = )
{
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 = ;
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("") && x.Status == ).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[].ItemArray;
//获取数据类型List
Object[] typeCell = sheet.Rows[].ItemArray;
//获取数据长度List
Object[] lengthCell = sheet.Rows[].ItemArray; for (int i = ; 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 = ; j < sheet.Rows.Count; j++)
{ Object[] cells = sheet.Rows[j].ItemArray;
bool isNullLine = true;
for (int i = ; 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 = ; i < dataTypes.Count; i++)
{ //必填项
if (dataRequired[i] == "必填项")
{
if (cells[i].ToString() == "" || cells[i].ToString() == null)
{
string messageB = "第" + (rowNum + ) + "行,第" + 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 + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{
isError = true;
AddLastMessage(ref message);
}
break;
//INT32类型数据验证
case "int":
if (!_tsysManagementBusiness.CheckInt32(cells[i].ToString(), "第" + (rowNum + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{
isError = true;
AddLastMessage(ref message);
}
break;
//INT64类型数据验证
case "bigint":
if (!_tsysManagementBusiness.CheckInt64(cells[i].ToString(), "第" + (rowNum + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{
isError = true;
AddLastMessage(ref message);
}
break;
//DECIMAL类型数据验证
case "decimal":
if (_tsysManagementBusiness.CheckDecimal(cells[i].ToString(), "第" + (rowNum + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{
if (!_tsysManagementBusiness.CheckDecimalLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + ) + "行,第" + 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 + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{
if (!_tsysManagementBusiness.CheckLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + ) + "行,第" + 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 + ) + "行,第" + 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 + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{ isError = true;
AddLastMessage(ref message);
}
break;
//INT32类型数据验证
case "int32":
if (!_tsysManagementBusiness.CheckInt32(cells[i].ToString(), "第" + (rowNum + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{
isError = true;
AddLastMessage(ref message);
}
break;
//INT64类型数据验证
case "int64":
if (!_tsysManagementBusiness.CheckInt64(cells[i].ToString(), "第" + (rowNum + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{
isError = true;
AddLastMessage(ref message);
}
break;
//DECIMAL类型数据验证
case "decimal":
if (_tsysManagementBusiness.CheckDecimal(cells[i].ToString(), "第" + (rowNum + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{
if (!_tsysManagementBusiness.CheckDecimalLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + ) + "行,第" + 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 + ) + "行,第" + ziMu[i] + "列", ref logMessage, ref message))
{
if (!_tsysManagementBusiness.CheckLength(cells[i].ToString(), dataLengths[i], "第" + (rowNum + ) + "行,第" + 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 + ) + "行,第" + 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 = ;//读取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("") && x.Status == ).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 == ).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 > )
{
foreach (var item in updateOrgList)
{
if (item.OrgName != CompanyName)
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(, , )))
{
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 = ; r < sheet.Rows.Count; r++)
{
Object[] currentRow = sheet.Rows[r].ItemArray;
if (String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
) { continue; }
Tuple<String, String, String> ThisRow = new Tuple<String, String, String>(
currentRow[].ToString(), //组织机构编码
currentRow[].ToString(), //组织名
currentRow[].ToString() //父组织机构编码
);
AllExcelData.Add(ThisRow);
AllTree.Add(new CheckTreeBModel() { Child = ThisRow.Item1, Parent = ThisRow.Item3, RowNumber = r + });
}
#endregion #region 父子级验证
List<CheckTreeBModel> TreeBModel = new List<CheckTreeBModel>();
TreeBModel = _tsysManagementBusiness.CheckTree(AllTree);
for (int i = ; 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 = ;
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 > )
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"] = ;
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 = ;//读取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 = ; i < ; 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("") && x.Status == ).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 = ; r < sheet.Rows.Count; r++)
{
Object[] currentRow = sheet.Rows[r].ItemArray;
if (
String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
)
{ continue; } #region read data row
DataRow ThisRow = Dt.NewRow();
ThisRow[] = currentRow[].ToString();//用户名UserName
ThisRow[] = currentRow[].ToString();//员工编号EmployeeCode
ThisRow[] = currentRow[].ToString(); //性别Gender
ThisRow[] = currentRow[].ToString(); //组织机构编码OrgId
ThisRow[] = currentRow[].ToString();//电话UserTel
ThisRow[] = currentRow[].ToString();//邮箱UserEmail
ThisRow[] = currentRow[].ToString();//登录ID
ThisRow[] = currentRow[].ToString();//密码
ThisRow[] = currentRow[].ToString();//系统角色AdminRole
ThisRow[] = currentRow[].ToString();//角色名称
ThisRow[] = currentRow[].ToString();//备注
#endregion #region check repeat
//员工编码check加在Excel
Dt.Rows.Add(ThisRow);
LogRowNumber.Add(r + );
#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 == "" && 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 == "" && t.Status == EntityStatusEnum.Active).ToList();
#endregion
for (int i = ; i < Dt.Rows.Count; i++)
{
TerpUserAndRole userAndRole = new TerpUserAndRole();
userAndRole.Id = Guid.NewGuid();
userAndRole.UserName = Dt.Rows[i][].ToString();
userAndRole.EmployeeCode = Dt.Rows[i][].ToString(); if (!string.IsNullOrEmpty(Dt.Rows[i][].ToString()))
{
TsysCompCode _gender = gender.FirstOrDefault(t => t.Item_Name.Equals(Dt.Rows[i][].ToString()));
if (_gender == null)
{
logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + () + "列" + "性别未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
userAndRole.Gender = _gender.Category_Code + _gender.Item_Code;
}
} if (!string.IsNullOrEmpty(Dt.Rows[i][].ToString()))
{
if (allOrg != null)
{
TsysCompOrg org = allOrg.Where(x => !String.IsNullOrEmpty(x.OrgName) && x.OrgName.Equals(Dt.Rows[i][].ToString())).FirstOrDefault();
if (org == null)
{
logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + () + "列" + "组织机构未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
userAndRole.OrgId = org.Id;
}
}
else
{
logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + () + "列" + "组织机构未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
} userAndRole.UserTel = Dt.Rows[i][].ToString();
userAndRole.UserEmail = Dt.Rows[i][].ToString();
userAndRole.LoginId = Dt.Rows[i][].ToString() + "@" + Mark; string psw = Dt.Rows[i][].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][].ToString()));
if (_adminRole == null)
{
logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + () + "列" + "系统角色未获取到";
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][].ToString(); if (String.IsNullOrEmpty(Dt.Rows[i][].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][].ToString())).FirstOrDefault();
if (CompRole == null)
{
logInfo = "第" + LogRowNumber[i].ToString() + "行" + "第" + () + "列" + "角色名称未获取到";
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"] = ;
row["RowVersion"] = default(byte[]);
row["CreatedDate"] = DateTime.Now;
row["CreatedBy"] = UserId;
row["UpdatedDate"] = DateTime.Now;
row["UpdatedBy"] = UserId;
row["Enable"] = "";
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"] = ;
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(, , )))
{
var dbset = _context.Set<TsysUser>().ToList();
for (int i = ; 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 = ;//读取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("") && x.Status == ).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 == ).ToList();
}
catch (Exception ex)
{ Logger.Information(ex.Message);
} if (CompRoleList.Count > )
{ 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 = ; r < sheet.Rows.Count; r++)
{
Object[] currentRow = sheet.Rows[r].ItemArray;
#region 读取行数据
if (String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
) { continue; } Tuple<String, String, String, String> ThisRow = new Tuple<String, String, String, String>( currentRow[].ToString(), //角色编码
currentRow[].ToString(), //角色名称
currentRow[].ToString(), //角色类型
currentRow[].ToString() //父角色编码
);
#endregion
//编码重复验证在Excel
AllExcelData.Add(ThisRow);
AllTree.Add(new CheckTreeBModel() { Child = ThisRow.Item1, Parent = ThisRow.Item4, RowNumber = r + });
AllRoleTree.Add(new CheckRoleTreeBModel() { Child = ThisRow.Item1, Parent = ThisRow.Item4, RowNumber = r + , 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 == "" && t.Status == EntityStatusEnum.Active).ToList();
#region 父子级验证,码表验证
List<CheckTreeBModel> TreeBModel = new List<CheckTreeBModel>();
TreeBModel = _tsysManagementBusiness.CheckTree(AllTree);
for (int i = ; 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() + "行" + () + "列" + "角色类型未获取到";
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(""))))
{
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 = ; 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 > )
{
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"] = ;
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 = ;//读取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("") && x.Status == ).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 + ];
secondSheet.Rows[].Delete();
secondSheet.AcceptChanges();
//政治状态
SaveCompanyCodeCategoryBySecondSheet(, secondSheet, "", compid);
//民族
SaveCompanyCodeCategoryBySecondSheet(, secondSheet, "", compid);
//在职状态
SaveCompanyCodeCategoryBySecondSheet(, secondSheet, "", compid);
//职称
SaveCompanyCodeCategoryBySecondSheet(, secondSheet, "", compid);
//银行
SaveCompanyCodeCategoryBySecondSheet(, secondSheet, "", compid);
//离职类型
SaveCompanyCodeCategoryBySecondSheet(, secondSheet, "", compid);
//户口性质
SaveCompanyCodeCategoryBySecondSheet(, secondSheet, "", compid);
//所属公司
SaveCompanyCodeCategoryBySecondSheet(, secondSheet, "", 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 == "" && 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 == "" && t.Status == EntityStatusEnum.Active).ToList(); // 政治面貌
List<TsysCompCode> PoliticsStatus = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "" && t.Status == EntityStatusEnum.Active).ToList(); // 民族
List<TsysCompCode> Nationality = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "" && t.Status == EntityStatusEnum.Active).ToList(); // 在职状态
List<TsysCompCode> workState = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "" && 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 == "" && t.Status == EntityStatusEnum.Active).ToList(); // 最高职称
List<TsysCompCode> workRank = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "" && t.Status == EntityStatusEnum.Active).ToList(); // 开户银行
List<TsysCompCode> Bank = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "" && t.Status == EntityStatusEnum.Active).ToList(); // 离职类型
List<TsysCompCode> dimissionType = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "" && t.Status == EntityStatusEnum.Active).ToList(); //户口性质
List<TsysCompCode> Nature = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "" && t.Status == EntityStatusEnum.Active).ToList(); //所属公司
List<TsysCompCode> CompanyName = _unitOfWork.Repository<TsysCompCode>().Get(t => t.CompanyId == compid && t.Category_Code == "" && t.Status == EntityStatusEnum.Active).ToList(); //读取数据
var sheet = excelReader.AsDataSet().Tables[sheetNum];
for (int r = ; r < sheet.Rows.Count; r++)
{
Object[] currentRow = sheet.Rows[r].ItemArray;
if (
String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString())
)
{ continue; } //员工编号重复验证在Excel TerpPersonInfo person = new TerpPersonInfo();
person.Id = Guid.NewGuid();
person.EmployeeCode = currentRow[].ToString();//员工编号
person.EmployeeName = currentRow[].ToString();//姓名 string orgName = currentRow[].ToString();//所属组织
if (allOrg == null || allOrg.Count == )
{
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 + ).ToString() + "行第3列所属组织未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.OrgId = org.Id;
} } TsysCompCode _gender = gender.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString())); //性别 码表
if (_gender == null)
{
logInfo = "第" + (r + ).ToString() + "行第4列性别未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.Gender = _gender.Category_Code + _gender.Item_Code;
} if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
person.Birthdate = Convert.ToDateTime(currentRow[].ToString());//出生日期
} if (!string.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _maritalStatus = maritalStatus.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString()));//婚姻状况 码表
if (_maritalStatus == null)
{
logInfo = "第" + (r + ).ToString() + "行第6列婚姻状况未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.MaritalStatus = _maritalStatus.Category_Code + _maritalStatus.Item_Code;
}
}
person.IdNumber = currentRow[].ToString();//身份证号 if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _PoliticsStatus = PoliticsStatus.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString()));//政治面貌 码表
if (_PoliticsStatus == null)
{
logInfo = "第" + (r + ).ToString() + "行第8列政治面貌未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.PoliticsStatus = _PoliticsStatus.Category_Code + _PoliticsStatus.Item_Code;
}
} if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _Nationality = Nationality.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString())); //民族 码表
if (_Nationality == null)
{
logInfo = "第" + (r + ).ToString() + "行第9列民族未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.Nationality = _Nationality.Category_Code + _Nationality.Item_Code;
}
} person.MobilePhone = currentRow[].ToString();//移动电话
person.InterPhone = currentRow[].ToString();//内线电话
person.GroupPhone = currentRow[].ToString();//办公电话
person.EmailAddress = currentRow[].ToString();//电子邮件
person.EmergencyContact = currentRow[].ToString();//紧急联系人
person.EmergencyTel = currentRow[].ToString();//紧急联系人电话
person.HomePhone = currentRow[].ToString();//家庭电话
person.HomeAddress = currentRow[].ToString();//家庭住址
person.Memo = currentRow[].ToString();//备注 if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _workState = workState.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString()));//在职状态 码表
if (_workState == null)
{
logInfo = "第" + (r + ).ToString() + "行第19列在职状态未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.WorkState = _workState.Category_Code + _workState.Item_Code;
}
} if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _employeeType = employeeType.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString())); //员工类型 码表 if (_employeeType == null)
{
logInfo = "第" + (r + ).ToString() + "行第20列员工类型未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.employeeType = _employeeType.Category_Code + _employeeType.Item_Code;
}
}
//参加工作日
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
person.WorkStartDate = Convert.ToDateTime(currentRow[].ToString());
}
//职务/岗位
person.Duty = currentRow[].ToString(); //加入工作日
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
person.TrialPeriodStartDate = Convert.ToDateTime(currentRow[].ToString());
}
//转正日期
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
person.RegularStartDate = Convert.ToDateTime(currentRow[].ToString());
}
//最高职称
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _workRank = workRank.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString()));//最高职称 码表
if (_workRank == null)
{
logInfo = "第" + (r + ).ToString() + "行第25列最高职称未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.WorkRank = _workRank.Category_Code + _workRank.Item_Code;
}
}
//开户银行
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _Bank = Bank.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString()));//开户银行 码表
if (_Bank == null)
{
logInfo = "第" + (r + ).ToString() + "行第26列开户银行未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.Bank = _Bank.Category_Code + _Bank.Item_Code;
}
}
person.BankCardNo = currentRow[].ToString();//银行账号
//离职日期
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
person.DimissionDate = Convert.ToDateTime(currentRow[].ToString());
}
//离职类型
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _dimissionType = dimissionType.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString()));
if (_dimissionType == null)
{
logInfo = "第" + (r + ).ToString() + "行第29列离职类型未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.DimissionType = _dimissionType.Category_Code + _dimissionType.Item_Code;
}
}
//离职原因
person.DimissionReason = currentRow[].ToString();
//户籍所在地
person.Domicile = currentRow[].ToString();
//参保时间
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
person.InsuredTime = Convert.ToDateTime(currentRow[].ToString());
}
//户口性质
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _Nature = Nature.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString()));
if (_Nature == null)
{
logInfo = "第" + (r + ).ToString() + "行第33列离职类型未获取到";
AddMessage(logInfo, ref logMessage);
AllCheckResult = false;
}
else
{
person.Nature = _Nature.Category_Code + _Nature.Item_Code;
}
}
//家庭成员
person.Memberoffamily = currentRow[].ToString();
//所属公司
if (!String.IsNullOrEmpty(currentRow[].ToString()))
{
TsysCompCode _CompanyName = CompanyName.FirstOrDefault(t => t.Item_Name.Equals(currentRow[].ToString()));
if (_CompanyName == null)
{
logInfo = "第" + (r + ).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"] = ;
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"] = "";
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"] = ;
userRow["RowVersion"] = default(byte[]);
userRow["CreatedDate"] = DateTime.Now;
userRow["CreatedBy"] = UserId;
userRow["UpdatedDate"] = DateTime.Now;
userRow["UpdatedBy"] = UserId;
userRow["Enable"] = "";
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"] = ;
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(, , )))
{
var dbset = _context.Set<TerpPersonInfo>().ToList();
var dbsetUser = _context.Set<TsysUser>().ToList();
for (int i = ; 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 = ;//读取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("") && x.Status == ).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 = ; r < sheet.Rows.Count; r++)
{
Object[] currentRow = sheet.Rows[r].ItemArray;
#region 获取行数据
//读行结束，跳出
if (String.IsNullOrEmpty(currentRow[].ToString())
&& String.IsNullOrEmpty(currentRow[].ToString()))
{
continue;
}
//读取一行客户数据
Tuple<String, String> ThisRow = new Tuple<String, String>(
currentRow[].ToString(),
currentRow[].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 + ) + "行" + "该员工编号下角色编码重复";
AddMessage(message, ref logMessage);
RepeatCheckResult = false;
AllCheckResult = false;
}
}
else
{
if (item.Item1.Equals(ThisRow.Item1) && item.Item2.Equals(ThisRow.Item2)) //员工编号和角色编码重复性验证
{
String message = "第" + (r + ) + "行" + "该员工编号下角色编码重复";
AddMessage(message, ref logMessage);
RepeatCheckResult = false;
AllCheckResult = false;
}
}
}
#endregion
if (RepeatCheckResult) //验证通过 存储一条
{
AllExcelData.Add(ThisRow);
LogRowNumber.Add(r + );
}
} #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 = ; 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"] = ;
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[];
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>";
} }
}