using DapperORMCore.Context.DataContext; using DapperORMCore.Dapper.BaseModel; using DapperORMCore.Model.CoreModel; using DapperORMCore.Repository.IRepositorys; using DapperORMCore.String.Consts; using Microsoft.Extensions.Configuration; using NXWMS.Code.Serialize; using NXWMS.DataAccess.Entity; using NXWMS.IService.NXWMS.Common; using NXWMS.Model.AppModels.Condition.Common; using NXWMS.Model.AppModels.Result; using NXWMS.Model.AppModels.Result.Common; using NXWMS.Model.AppModels.View.Common; using NXWMS.Model.Common; using NXWMS.String.Enums; using Org.BouncyCastle.Asn1.Crmf; using Org.BouncyCastle.Asn1.Mozilla; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; using WestDistance.DapperORM.Repository.Repositorys; namespace NXWMS.Service.NXWMS.Common { /// /// 临时导入服务 /// [AutoInject(typeof(ITempImportService), InjectType.Scope)] public class TempImportService : ServiceBase, ITempImportService { /// /// 系统操作仓储中转 /// private IDataRepositoryContext _dataContext; /// /// SQL节点仓储 /// private ISQLNodeRepository _iSQLNodeRepository; /// /// 配置 /// private IConfiguration _configuration; public TempImportService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository) { this._dataContext = dataRepositoryContext; this._configuration = configuration; this._iSQLNodeRepository = iSQLNodeRepository; } public OperateResultInfo Add(TempImportCondition info) { var now = DateTime.Now; int affectedRows = 0; try { _dataContext.BeginTran(); foreach (var item in info.DetailList) { if (new DataRepository(_dataContext).Query("BATCH_NO", item.BatchNo).FirstOrDefault() != null) { continue; } var entity = new TEMP_IMPORT_DATA { CREATE_BY = info.OperationUserId, CREATE_TIME = now, UPDATE_BY = info.OperationUserId, UPDATE_TIME = now, IMPORT_DATA_JSON = item.DataJson.ToJson(), DESCRIBE = item.Describe, IMPORT_DATA_NUMBER = item.DataJson.Rows.Count, IMPORT_TEMPLATE_ID = item.ImportTemplateId, USED_FLAG = 1, BATCH_NO = item.BatchNo, IMPORT_STATUS = (int)TempImportStatus.Init, DEL_FLAG = 0, }; affectedRows = affectedRows + new DataRepository(_dataContext).Add(entity); } } catch (Exception ex) { _dataContext.Rollback(); return FailStatus(ex.Message); } _dataContext.Commit(); return SuccessStatus(); } public OperateResultInfo Exec(TempImportExecCondition info) { if (string.IsNullOrWhiteSpace(info.BatchNo)) { return FailStatus("参数错误!"); } //每次只能执行一个批次数据 try { var sql = string.Empty; var now = DateTime.Now; var affectedRows = 0; _dataContext.BeginTran(); var importDataesult = new DataRepository(_dataContext).Query("BATCH_NO", info.BatchNo); //导入数据遍历 foreach (var item in importDataesult) { switch (info.TempImportExecType) { case TempImportExecType.Check: if (item.IMPORT_STATUS == (int)TempImportStatus.Init || item.IMPORT_STATUS == (int)TempImportStatus.CheckFail) { //导入前将状态改成检查中... var updateEntity = new TEMP_IMPORT_DATA { TEMP_IMPORT_DATA_ID = item.TEMP_IMPORT_DATA_ID, IMPORT_STATUS = (int)TempImportStatus.Checking, UPDATE_BY = info.OperationUserId, UPDATE_TIME = now, }; affectedRows = affectedRows + new DataRepository(_dataContext).Update(updateEntity, "TEMP_IMPORT_DATA_ID", "NEWID"); sql = string.Format(sql, item.TEMP_IMPORT_DATA_ID); var mainResult = new DataRepository(_dataContext).Query("IMPORT_TEMPLATE_ID", item.IMPORT_TEMPLATE_ID.ToString()).FirstOrDefault(); var detailList = new DataRepository(_dataContext).Query("IMPORT_TEMPLATE_ID", item.IMPORT_TEMPLATE_ID.ToString()); var data = item.IMPORT_DATA_JSON.ToObject(); data.PrimaryKey = new DataColumn[] { data.Columns[0] }; var result = ImportData(detailList.ToList(), mainResult, data, TempImportExecType.Check); if (result.Status == OperateStatus.Success) { item.IMPORT_STATUS = (int)TempImportStatus.CheckSuccess; } else { item.IMPORT_STATUS = (int)TempImportStatus.CheckFail; } item.UPDATE_BY = info.OperationUserId; item.UPDATE_TIME = now; item.LAST_CHECK_TIME = now; item.LAST_CHECK_MESSAGE = result.Message; affectedRows = affectedRows + new DataRepository(_dataContext).Update(item, "TEMP_IMPORT_DATA_ID", "NEWID"); } break; case TempImportExecType.Import: if (item.IMPORT_STATUS == (int)TempImportStatus.CheckSuccess) { //导入前将状态改成检查中... var updateEntity = new TEMP_IMPORT_DATA { TEMP_IMPORT_DATA_ID = item.TEMP_IMPORT_DATA_ID, IMPORT_STATUS = (int)TempImportStatus.Importing, UPDATE_BY = info.OperationUserId, UPDATE_TIME = now, }; affectedRows = affectedRows + new DataRepository(_dataContext).Update(item, "TEMP_IMPORT_DATA_ID", "NEWID"); sql = string.Format(sql, item.TEMP_IMPORT_DATA_ID); var printTemplateList = new DataRepository(_dataContext).Query(sql); var mainResult = new DataRepository(_dataContext).Query("IMPORT_TEMPLATE_ID", item.IMPORT_TEMPLATE_ID.ToString()).FirstOrDefault(); var detailList = new DataRepository(_dataContext).Query("IMPORT_TEMPLATE_ID", item.IMPORT_TEMPLATE_ID.ToString()); var data = item.IMPORT_DATA_JSON.ToObject(); data.PrimaryKey = new DataColumn[] { data.Columns[0] }; var result = ImportData(detailList.ToList(), mainResult, data, TempImportExecType.Import); if (result.Status == OperateStatus.Success) { item.IMPORT_STATUS = (int)TempImportStatus.ImportSuccess; } else { item.IMPORT_STATUS = (int)TempImportStatus.ImportFail; } item.UPDATE_BY = info.OperationUserId; item.UPDATE_TIME = now; item.LAST_IMPORT_TIME = now; item.LAST_IMPORT_MESSAGE = result.Message; affectedRows = affectedRows + new DataRepository(_dataContext).Update(item, "TEMP_IMPORT_DATA_ID", "NEWID"); } break; } } } catch (Exception ex) { _dataContext.Rollback(); return FailMessageStatus(ex.Message); } _dataContext.Commit(); return SuccessStatus(); } /// /// 执行逻辑 /// /// 导入模版明细 /// 导入模版主 /// 数据 /// 执行类型 /// public OperateResultInfo ImportData(List printTemplateDetailList, BAS_IMPORT_TEMPLATE printTemplateMain, DataTable data, TempImportExecType importExecType) { switch (importExecType) { //检查 case TempImportExecType.Check: var messageList = new List(); //必填验证 foreach (var configItem in printTemplateDetailList) { if (configItem.TARGET_REQUIRED_FLAG == (int)RequiredFlag.YES) { if (!data.Rows.Contains(configItem.SOURCE_NAME)) { if (string.IsNullOrWhiteSpace(data.Rows[0][configItem.SOURCE_NAME].ToString())) { messageList.Add(configItem.TARGET_NAME + "要求必须填写!"); } } } } if (messageList.Any()) { return FailMessageStatus(string.Join(",", messageList)); } //数据验证 messageList = new List(); for (int i = 0; i < data.Rows.Count; i++) { messageList = new List(); foreach (var configItem in printTemplateDetailList) { var value = data.Rows[i][configItem.SOURCE_NAME].ToString(); //长度 if (configItem.TARGET_LENGTH != null) { if (configItem.TARGET_LENGTH > 0) { if (value.Length > configItem.TARGET_LENGTH.Value) { messageList.Add($"第{i + 1}行数据,{configItem.SOURCE_NAME}长度要求<={configItem.TARGET_LENGTH.Value}!"); } } } //类型 try { switch (configItem.TARGET_TYPE) { case "Int": Convert.ToInt32(data.Rows[i][configItem.SOURCE_NAME]); break; case "Numeric": Convert.ToDouble(data.Rows[i][configItem.SOURCE_NAME]); break; case "Boolean": Convert.ToBoolean(data.Rows[i][configItem.SOURCE_NAME]); break; case "DateTime": Convert.ToDateTime(data.Rows[i][configItem.SOURCE_NAME]); break; } } catch (Exception ex) { messageList.Add($"第{i + 1}行数据,{configItem.SOURCE_NAME}无法转换成设定的数据类型!"); } //关联 if (configItem.TARGET_RELATION_TYPE == ImportTemplateRelationType.ForeignKey.ToString()) { var sql = $"SELECT TOP 1 KEY,VALUE FROM ({configItem.TARGET_RELATION_CONTENT}) TEMP WHERE " + $"TEMP.KEY ='{value}' OR TEMP.NAME='{value}'"; var keyValue = new DataRepository(_dataContext).Query(sql).FirstOrDefault(); if (keyValue == null) { messageList.Add($"第{i + 1}行数据,{configItem.SOURCE_NAME}不满足关联类型外键!"); } } } } //存在错误消息 if (messageList.Any()) { return FailMessageStatus(string.Join(",", messageList)); } return SuccessStatus(); //导入 case TempImportExecType.Import: try { var fieldList = new List(); foreach (var configItem in printTemplateDetailList) { if (data.Columns.Contains(configItem.SOURCE_CODE)) { fieldList.Add(configItem.TARGET_CODE); } } var affectedRows = 0; for (int i = 0; i < data.Rows.Count; i++) { var valueList = new List(); foreach (var configItem in printTemplateDetailList) { if (data.Columns.Contains(configItem.SOURCE_CODE)) { switch (configItem.TARGET_TYPE) { case "Int": case "Numeric": case "Boolean": valueList.Add(configItem.TARGET_CODE); break; default: valueList.Add("'" + configItem.TARGET_CODE + "'"); break; } } } var sql = $"INSERT INTO {printTemplateMain.TARGET_OBJECT_CODE}({fieldList}) Value({valueList})"; affectedRows = affectedRows + new DataRepository(_dataContext).Execute(sql); } if (affectedRows != data.Rows.Count) { return GetStatus(affectedRows, data.Rows.Count); } return SuccessStatus(); } catch (Exception ex) { return FailMessageStatus(ex.Message); } } return FailMessageStatus("参数错误!"); } public OperateResultInfo> GetList(TempImportSearchCondition info) { var sqlAndBuilder = new StringBuilder(); var sqlOrBuilder = new StringBuilder(); var sql = $@"SELECT CreateName = (SELECT USER_NAME FROM SYS_USER A WHERE A.USER_ID=CREATE_BY), UpdateName = (SELECT USER_NAME FROM SYS_USER B WHERE B.USER_ID=UPDATE_BY), ImportMode = BAS_IMPORT_TEMPLATE.IMPORT_MODE, ImportModeName = {_iSQLNodeRepository.GetEnumIntCaseString("IMPORT_MODE")}, ImportStatusName = {_iSQLNodeRepository.GetEnumIntCaseString("IMPORT_STATUS")}, ImportTemplateCode = BAS_IMPORT_TEMPLATE.IMPORT_TEMPLATE_CODE, ImportTemplateName = BAS_IMPORT_TEMPLATE.IMPORT_TEMPLATE_NAME, TargetObjectName = BAS_IMPORT_TEMPLATE.TARGET_OBJECT_NAME, TargetObjectCode = BAS_IMPORT_TEMPLATE.TARGET_OBJECT_CODE, TargetObjectType = BAS_IMPORT_TEMPLATE.TARGET_OBJECT_TYPE, TargetObjectTypeName = {_iSQLNodeRepository.GetEnumIntCaseString("TARGET_OBJECT_TYPE")}, TEMP_IMPORT_DATA.* FROM TEMP_IMPORT_DATA JOIN BAS_IMPORT_TEMPLATE ON BAS_IMPORT_TEMPLATE.IMPORT_TEMPLATE_ID=TEMP_IMPORT_DATA.IMPORT_TEMPLATE_ID WHERE 1=1 AND TEMP_IMPORT_DATA.DEL_FLAG = 0"; sqlAndBuilder = string.IsNullOrWhiteSpace(info.BatchNo) ? sqlAndBuilder : sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("BATCH_NO", info.BatchNo, DBOperationString._ContainIn)); sqlAndBuilder = info.ImportStatus == null ? sqlAndBuilder : sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IMPORT_STATUS", info.ImportStatus, DBOperationString._ContainIn)); sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : ""); //检查时间 sqlOrBuilder = info.CheckBeginTime == null ? sqlOrBuilder : sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("LAST_CHECK_TIME", info.CheckBeginTime, DBOperationString._LargeEqual)); sqlOrBuilder = info.CheckEndTime == null ? sqlOrBuilder : sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("LAST_CHECK_TIME", info.CheckEndTime, DBOperationString._SmallEqual)); sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : ""); //导入时间 sqlOrBuilder = info.ImportBeginTime == null ? sqlOrBuilder : sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("LAST_IMPORT_TIME", info.ImportBeginTime, DBOperationString._LargeEqual)); sqlOrBuilder = info.ImportEndTime == null ? sqlOrBuilder : sqlOrBuilder.Append(_iSQLNodeRepository.GetOrCondition("LAST_IMPORT_TIME", info.ImportEndTime, DBOperationString._SmallEqual)); sql = sql + (sqlOrBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlOrBuilder, false) : ""); IEnumerable result; IEnumerable totalResult; totalResult = new DataRepository(_dataContext).Query(sql); if (info.PageIndex == 0 || info.PageSize == 0) { result = totalResult.ToList(); } else { result = new DataRepository(_dataContext).QueryPage(sql, "UPDATE_TIME", info.PageSize, info.PageIndex, true); } return SuccessStatus(new PageQueryResultInfo { RowData = result, PageConditionInfo = info, TotalCount = totalResult.Count(), TotalPageCount = (int)Math.Ceiling((double)totalResult.Count() / info.PageSize) }); } } }