Wms_WebApi/NXWMS.WebApi/NXWMS.Service/NXWMS/Common/TempImportService.cs

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
{
    /// <summary>
    /// 临时导入服务
    /// </summary>
    [AutoInject(typeof(ITempImportService), InjectType.Scope)]
    public class TempImportService : ServiceBase, ITempImportService
    {
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

        /// <summary>
        /// SQL节点仓储
        /// </summary>
        private ISQLNodeRepository _iSQLNodeRepository;

        /// <summary>
        /// 配置
        /// </summary>
        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<TEMP_IMPORT_DATA>(_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<TEMP_IMPORT_DATA>(_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<TEMP_IMPORT_DATA>(_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<TEMP_IMPORT_DATA>(_dataContext).Update(updateEntity, "TEMP_IMPORT_DATA_ID", "NEWID");
                                sql = string.Format(sql, item.TEMP_IMPORT_DATA_ID);
                                var mainResult = new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Query("IMPORT_TEMPLATE_ID", item.IMPORT_TEMPLATE_ID.ToString()).FirstOrDefault();
                                var detailList = new DataRepository<BAS_IMPORT_TEMPLATE_DTL>(_dataContext).Query("IMPORT_TEMPLATE_ID", item.IMPORT_TEMPLATE_ID.ToString());
                                var data = item.IMPORT_DATA_JSON.ToObject<DataTable>();

                                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<TEMP_IMPORT_DATA>(_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<TEMP_IMPORT_DATA>(_dataContext).Update(item, "TEMP_IMPORT_DATA_ID", "NEWID");

                                sql = string.Format(sql, item.TEMP_IMPORT_DATA_ID);
                                var printTemplateList = new DataRepository<ImportTemplateView>(_dataContext).Query(sql);
                                var mainResult = new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Query("IMPORT_TEMPLATE_ID", item.IMPORT_TEMPLATE_ID.ToString()).FirstOrDefault();
                                var detailList = new DataRepository<BAS_IMPORT_TEMPLATE_DTL>(_dataContext).Query("IMPORT_TEMPLATE_ID", item.IMPORT_TEMPLATE_ID.ToString());
                                var data = item.IMPORT_DATA_JSON.ToObject<DataTable>();
                                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<TEMP_IMPORT_DATA>(_dataContext).Update(item, "TEMP_IMPORT_DATA_ID", "NEWID");
                            } 
                            break;
                    }
                } 
            }
            catch (Exception ex)
            {
                _dataContext.Rollback();
                return FailMessageStatus(ex.Message);
            }
            _dataContext.Commit();
            return SuccessStatus();
        }

        /// <summary>
        /// 执行逻辑
        /// </summary>
        /// <param name="printTemplateDetailList">导入模版明细</param>
        /// <param name="printTemplateMain">导入模版主</param>
        /// <param name="data">数据</param>
        /// <param name="importExecType">执行类型</param>
        /// <returns></returns>
        public OperateResultInfo ImportData(List<BAS_IMPORT_TEMPLATE_DTL> printTemplateDetailList,
                        BAS_IMPORT_TEMPLATE printTemplateMain, DataTable data, TempImportExecType importExecType)
        {
            switch (importExecType)
            {
                //检查
                case TempImportExecType.Check:
                    var messageList = new List<string>();
                    //必填验证
                    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<string>();
                    for (int i = 0; i < data.Rows.Count; i++)
                    {
                        messageList = new List<string>();
                        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<KeyValueView>(_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<string>();
                        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<string>();
                            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<object>(_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<PageQueryResultInfo<TempImportResult>> 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<ImportTemplateMode>("IMPORT_MODE")}, 
                        ImportStatusName = {_iSQLNodeRepository.GetEnumIntCaseString<TempImportStatus>("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<SourceObjectType>("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<TempImportResult> result;
            IEnumerable<TempImportResult> totalResult;
            totalResult = new DataRepository<TempImportResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<TempImportResult>(_dataContext).QueryPage(sql,
                                                "UPDATE_TIME", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<TempImportResult>
            {
                RowData = result,
                PageConditionInfo = info,
                TotalCount = totalResult.Count(),
                TotalPageCount = (int)Math.Ceiling((double)totalResult.Count() / info.PageSize)
            });
        }
    }
}