Wms_WebApi/NXWMS.WebApi/NXWMS.Service/NXWMS/Rule/ImportTemplateService.cs

using DapperORMCore.Context.DataContext;
using DapperORMCore.Model.BaseModel;
using DapperORMCore.Model.CoreModel;
using DapperORMCore.Repository.IRepositorys;
using DapperORMCore.String.Consts;
using DapperORMCore.String.Enums;
using Microsoft.Extensions.Configuration; 
using NXWMS.Code;
using NXWMS.DataAccess.Entity;
using NXWMS.IService.NXWMS.Rule;
using NXWMS.Model.AppModels.Condition.Rule;
using NXWMS.Model.AppModels.Result.Rule;
using NXWMS.Model.Common;
using NXWMS.String.Enums;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WestDistance.DapperORM.Repository.Repositorys;

namespace NXWMS.Service.NXWMS.Rule
{
    /// <summary>
    /// 导入模版
    /// </summary>
    [AutoInject(typeof(IImportTemplateService), InjectType.Scope)]
    public class ImportTemplateService : ServiceBase, IImportTemplateService
    {
        /// <summary>
        /// 系统操作仓储中转
        /// </summary>
        private IDataRepositoryContext _dataContext;

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

        /// <summary>
        /// 配置
        /// </summary>
        private IConfiguration _configuration;

        public ImportTemplateService(IDataRepositoryContext dataRepositoryContext, IConfiguration configuration, ISQLNodeRepository iSQLNodeRepository)
        {
            this._dataContext = dataRepositoryContext;
            this._configuration = configuration;
            this._iSQLNodeRepository = iSQLNodeRepository;
        }

        public OperateResultInfo<List<ImportTemplateDetailResult>> GetDetailList(ImportTemplateDetailSearchCondition info)
        {
            if (string.IsNullOrWhiteSpace(info.TemplateCode))
            {
                return FailMessageStatus("参数错误！", new List<ImportTemplateDetailResult>());
            }
            var sqlAndBuilder = 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), 
                        UsedFlagName = {_iSQLNodeRepository.GetEnumIntCaseString<UsedFlag>("USED_FLAG", "BAS_IMPORT_TEMPLATE")}, 
                        SourceTypeName =  {_iSQLNodeRepository.GetEnumStrCaseString<SourceType>("SOURCE_TYPE")}, 
                        SourceRequiredFlagName =  {_iSQLNodeRepository.GetEnumIntCaseString<RequiredFlag>("SOURCE_REQUIRED_FLAG")}, 
                        TargetTypeName =  {_iSQLNodeRepository.GetEnumStrCaseString<TargetType>("TARGET_TYPE")}, 
                        TargetRequiredFlagName =  {_iSQLNodeRepository.GetEnumIntCaseString<RequiredFlag>("TARGET_REQUIRED_FLAG")}, 
                        TargetRelationTypeName =  {_iSQLNodeRepository.GetEnumStrCaseString<ImportTemplateRelationType>("TARGET_RELATION_TYPE")}, 
                        BAS_IMPORT_TEMPLATE.IMPORT_TEMPLATE_CODE,BAS_IMPORT_TEMPLATE.IMPORT_TEMPLATE_NAME,
                        BAS_IMPORT_TEMPLATE_DTL.*
                        FROM BAS_IMPORT_TEMPLATE_DTL 
                        JOIN BAS_IMPORT_TEMPLATE ON  BAS_IMPORT_TEMPLATE_DTL.IMPORT_TEMPLATE_ID=BAS_IMPORT_TEMPLATE_DTL.IMPORT_TEMPLATE_ID
                        WHERE 1=1 AND BAS_IMPORT_TEMPLATE.DEL_FLAG = 0 AND BAS_IMPORT_TEMPLATE.USED_FLAG=1 AND BAS_IMPORT_TEMPLATE_DTL.USED_FLAG=1
                        AND BAS_IMPORT_TEMPLATE.IMPORT_TEMPLATE_CODE='{info.TemplateCode}'";
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");

            var result = new DataRepository<ImportTemplateDetailResult>(_dataContext).Query(sql);
            return SuccessStatus(result.ToList());
        }

        public OperateResultInfo<PageQueryResultInfo<ImportTemplateResult>> GetList(ImportTemplateSearchCondition 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), 
                        UsedFlagName = {_iSQLNodeRepository.GetEnumIntCaseString<UsedFlag>("USED_FLAG")}, 
                        ImportModeName = {_iSQLNodeRepository.GetEnumStrCaseString<ImportTemplateMode>("IMPORT_MODE")}, 
                        SourceObjTypeName  = {_iSQLNodeRepository.GetEnumStrCaseString<SourceObjectType>("SOURCE_OBJECT_TYPE")}, 
                        {info.ItemSQL} FROM BAS_IMPORT_TEMPLATE WHERE 1=1 AND DEL_FLAG = 0";
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.TemplateCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IMPORT_TEMPLATE_CODE", info.TemplateCode, DBOperationString._ContainIn));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.TemplateName) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IMPORT_TEMPLATE_NAME", info.TemplateName, DBOperationString._ContainIn));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.SourceObjName) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("SOURCE_OBJECT_NAME", info.SourceObjName, DBOperationString._ContainIn));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.ImportMode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IMPORT_MODE", info.ImportMode, DBOperationString._Equal));
            sqlAndBuilder = info.IsUsed == null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("USED_FLAG", info.IsUsed, DBOperationString._Equal));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");

            IEnumerable<ImportTemplateResult> result;
            IEnumerable<ImportTemplateResult> totalResult;
            totalResult = new DataRepository<ImportTemplateResult>(_dataContext).Query(sql);
            if (info.PageIndex == 0 || info.PageSize == 0)
            {
                result = totalResult.ToList();
            }
            else
            {
                result = new DataRepository<ImportTemplateResult>(_dataContext).QueryPage(sql,
                                                "CREATE_TIME", info.PageSize, info.PageIndex, true);
            }
            return SuccessStatus(new PageQueryResultInfo<ImportTemplateResult>
            {
                RowData = result,
                PageConditionInfo = info,
                TotalCount = totalResult.Count(),
                TotalPageCount = (int)Math.Ceiling((double)totalResult.Count() / info.PageSize)
            });
        }

        public OperateResultInfo Add(ImportTemplateCondition info)
        {
            if (new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Query().Where(m => m.IMPORT_TEMPLATE_CODE == info.TemplateCode
                                             && m.DEL_FLAG == 0 && m.USED_FLAG == 1).Any())
            {
                return FailMessageStatus("导入模版编码已经存在,请输入其它编码!");
            }

            var affectedRows = 0;
            try
            {
                _dataContext.BeginTran();
                var now = DateTime.Now;
                var templateId =
                    new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Query().Count() == 0 ? 1 :
                    new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Query().Select(s => s.IMPORT_TEMPLATE_ID).Max() + 1;
                var entity = new BAS_IMPORT_TEMPLATE
                {
                    CREATE_BY = info.OperationUserId,
                    CREATE_TIME = now,
                    UPDATE_BY = info.OperationUserId,
                    UPDATE_TIME = now,
                    USED_FLAG = Convert.ToInt32(info.IsUsed),
                    DESCRIBE = info.Describe,
                    DEL_FLAG = 0,
                    IMPORT_MODE = info.ImportMode,
                    IMPORT_TEMPLATE_CODE = info.TemplateCode,
                    TARGET_OBJECT_CODE = info.SourceObjCode,
                    TARGET_OBJECT_NAME = info.SourceObjName,
                    TARGET_OBJECT_TYPE = info.SourceObjType,
                    IMPORT_TEMPLATE_NAME = info.TemplateName,
                    IMPORT_TEMPLATE_ID = templateId,
                };
                new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Add(entity);
                foreach (var item in info.DetailList)
                {
                    var permission = new BAS_IMPORT_TEMPLATE_DTL
                    {
                        CREATE_TIME = now,
                        UPDATE_TIME = now,
                        CREATE_BY = info.OperationUserId,
                        UPDATE_BY = info.OperationUserId,
                        DEL_FLAG = 0,
                        DESCRIBE = item.Describe,
                        IMPORT_TEMPLATE_CODE = info.TemplateCode,
                        IMPORT_TEMPLATE_ID = templateId,
                        SOURCE_CODE = item.SourceCode,
                        SOURCE_REQUIRED_FLAG = item.SourceRequiredFlag,
                        SOURCE_LENGTH = item.SourceLength,
                        SOURCE_NAME = item.SourceName,
                        SOURCE_TYPE = item.SourceType,
                        TARGET_CODE = item.TargetCode,
                        TARGET_LENGTH = item.TargetLength,
                        TARGET_NAME = item.TargetName,
                        TARGET_RELATION_CONTENT = item.TargetRelationContent,
                        TARGET_RELATION_TYPE = item.TargetRelationType,
                        TARGET_REQUIRED_FLAG = item.TargetRequiredFlag,
                        TARGET_TYPE = item.TargetType,
                        USED_FLAG = 1,
                    };
                    affectedRows = affectedRows + new DataRepository<BAS_IMPORT_TEMPLATE_DTL>(_dataContext).Add(permission,
                                new string[1] { "IMPORT_TEMPLATE_DTL_ID" });
                }
                if (affectedRows != info.DetailList.Count)
                {
                    _dataContext.Rollback();
                    return GetStatus(affectedRows, info.DetailList.Count);
                }
            }
            catch (Exception ex)
            {
                _dataContext.Rollback();
                return FailStatus(ex.Message);
            }
            _dataContext.Commit();
            return GetStatus(affectedRows);
        }

        public OperateResultInfo Deleted(ImportTemplateCondition info)
        {
            var sqlAndBuilder = new StringBuilder();
            var sql = $@"SELECT {info.ItemSQL} FROM BAS_IMPORT_TEMPLATE WHERE 1=1 AND DEL_FLAG = 0 ";
            sqlAndBuilder = info.TemplateId == null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IMPORT_TEMPLATE_ID", info.TemplateId, DBOperationString._Equal));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.TemplateCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IMPORT_TEMPLATE_CODE", info.TemplateCode, DBOperationString._Equal));
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.TemplateCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IMPORT_TEMPLATE_ID", info.TemplateIds, DBOperationString._In));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");
            var result = new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Query(sql);
            if (!result.Any())
            {
                return FailMessageStatus("参数错误");
            }
            var now = DateTime.Now;
            var affectedRows = 0;
            foreach(var item in result)
            {
                sql = $@"UPDATE BAS_IMPORT_TEMPLATE SET DEL_FLAG=1,UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE IMPORT_TEMPLATE_ID={item.IMPORT_TEMPLATE_ID}";
                affectedRows = new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Execute(sql);
                sql = $@"UPDATE BAS_IMPORT_TEMPLATE_DTL SET DEL_FLAG=1,UPDATE_BY={info.OperationUserId},UPDATE_TIME='{now}'
                        WHERE IMPORT_TEMPLATE_ID={item.IMPORT_TEMPLATE_ID}";
                affectedRows = new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Execute(sql);
            }   
            return SuccessStatus();
        }

        public OperateResultInfo Edit(ImportTemplateCondition info)
        {
            if (!new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Query().Where(m => m.IMPORT_TEMPLATE_CODE == info.TemplateCode
                                                         && m.DEL_FLAG == 0 && m.USED_FLAG == 1).Any())
            {
                return FailMessageStatus("导入模版编码不存在,请检查!");
            }

            var affectedRows = 0;
            try
            {
                _dataContext.BeginTran();
                var now = DateTime.Now;

                var whereList = new List<FieldKeyInfo>()
                                            .AddFieldKeyInfo(nameof(BAS_IMPORT_TEMPLATE.IMPORT_TEMPLATE_CODE),
                                                info.TemplateCode, EnumCSharpPropertyType.STRING, DBOperationString._Equal,
                                                !string.IsNullOrWhiteSpace(info.TemplateCode))
                                            .AddFieldKeyInfo(nameof(BAS_IMPORT_TEMPLATE.IMPORT_TEMPLATE_ID),
                                                info.TemplateId, EnumCSharpPropertyType.INT, DBOperationString._Equal,
                                                info.TemplateId != null);
                var result = new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Query(whereList).FirstOrDefault();
                if (result == null)
                {
                    return FailMessageStatus("参数错误");
                }

                var entity = new BAS_IMPORT_TEMPLATE
                {
                    IMPORT_TEMPLATE_ID = result.IMPORT_TEMPLATE_ID,
                    DESCRIBE = info.Describe,
                    USED_FLAG = info.IsUsed ? 1 : 0
                };
                new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Update(entity, "IMPORT_TEMPLATE_ID", "NEWID");

                foreach (var item in new DataRepository<BAS_IMPORT_TEMPLATE_DTL>(_dataContext).Query("IMPORT_TEMPLATE_ID",
                                result.IMPORT_TEMPLATE_ID.ToString()).ToList())
                {
                    var entitydtl = new BAS_IMPORT_TEMPLATE_DTL
                    {
                        IMPORT_TEMPLATE_ID = result.IMPORT_TEMPLATE_ID,
                        UPDATE_TIME = now,
                        DEL_FLAG = 1,
                        UPDATE_BY = info.OperationUserId,
                        IMPORT_TEMPLATE_DTL_ID = item.IMPORT_TEMPLATE_DTL_ID,
                    };
                    new DataRepository<BAS_IMPORT_TEMPLATE_DTL>(_dataContext).Update(entitydtl, "IMPORT_TEMPLATE_DTL_ID", "NEWID");
                }

                foreach (var item in info.DetailList)
                {
                    //新增
                    if (item.TemplateDetailId == null)
                    {
                        var dtlEntity = new BAS_IMPORT_TEMPLATE_DTL
                        {
                            CREATE_TIME = now,
                            UPDATE_TIME = now,
                            CREATE_BY = info.OperationUserId,
                            UPDATE_BY = info.OperationUserId,
                            DEL_FLAG = 0,
                            DESCRIBE = item.Describe,
                            IMPORT_TEMPLATE_CODE = result.IMPORT_TEMPLATE_CODE,
                            IMPORT_TEMPLATE_ID = result.IMPORT_TEMPLATE_ID,
                            SOURCE_CODE = item.SourceCode,
                            SOURCE_LENGTH = item.SourceLength,
                            SOURCE_NAME = item.SourceName,
                            SOURCE_TYPE = item.SourceType,
                            SOURCE_REQUIRED_FLAG = item.SourceRequiredFlag,
                            TARGET_CODE = item.TargetCode,
                            TARGET_LENGTH = item.TargetLength,
                            TARGET_NAME = item.TargetName,
                            TARGET_RELATION_CONTENT = item.TargetRelationContent,
                            TARGET_RELATION_TYPE = item.TargetRelationType,
                            TARGET_REQUIRED_FLAG = item.TargetRequiredFlag,
                            TARGET_TYPE = item.TargetType,
                            USED_FLAG = 1,
                        };
                        affectedRows = affectedRows + new DataRepository<BAS_IMPORT_TEMPLATE_DTL>
                                    (_dataContext).Add(dtlEntity, new string[1] { "IMPORT_TEMPLATE_DTL_ID" });
                    }
                    //修改
                    else
                    {
                        var itemDetail = new DataRepository<BAS_IMPORT_TEMPLATE_DTL>(_dataContext).Query("IMPORT_TEMPLATE_DTL_ID",
                                    item.TemplateDetailId.ToString()).FirstOrDefault();
                        itemDetail.SOURCE_CODE = item.SourceCode;
                        itemDetail.SOURCE_LENGTH = item.SourceLength;
                        itemDetail.SOURCE_NAME = item.SourceName;
                        itemDetail.SOURCE_TYPE = item.SourceType;
                        itemDetail.SOURCE_REQUIRED_FLAG = item.SourceRequiredFlag;
                        itemDetail.TARGET_CODE = item.TargetCode;
                        itemDetail.TARGET_LENGTH = item.TargetLength;
                        itemDetail.TARGET_NAME = item.TargetName;
                        itemDetail.TARGET_RELATION_CONTENT = item.TargetRelationContent;
                        itemDetail.TARGET_RELATION_TYPE = item.TargetRelationType;
                        itemDetail.TARGET_REQUIRED_FLAG = item.TargetRequiredFlag;
                        itemDetail.TARGET_TYPE = item.TargetType;
                        itemDetail.UPDATE_BY = info.OperationUserId;
                        itemDetail.DEL_FLAG = 0;
                        itemDetail.UPDATE_TIME = now;
                        affectedRows = affectedRows + new DataRepository<BAS_RULE_DTL>(_dataContext).Update(itemDetail,
                                 "RULE_DTL_ID", "NEWID");
                    }

                }
                if (affectedRows != info.DetailList.Count)
                {
                    _dataContext.Rollback();
                    return GetStatus(affectedRows, info.DetailList.Count);
                }
                _dataContext.Commit();
                return SuccessStatus();
            }
            catch (Exception ex)
            {
                _dataContext.Rollback();
                return FailStatus(ex.Message);
            }
        }

        public OperateResultInfo Remove(ImportTemplateCondition info)
        {
            if (string.IsNullOrWhiteSpace(info.TemplateCode) && info.TemplateId == null)
            {
                return FailMessageStatus("参数错误!");
            }

            var sqlAndBuilder = new StringBuilder();
            var sqlOrBuilder = new StringBuilder();
            var sql = $@"SELECT {info.ItemSQL} FROM BAS_IMPORT_TEMPLATE WHERE 1=1 AND DEL_FLAG = 0 ";
            sqlAndBuilder = string.IsNullOrWhiteSpace(info.TemplateCode) ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IMPORT_TEMPLATE_CODE", info.TemplateCode, DBOperationString._Equal));
            sqlAndBuilder = info.TemplateId != null ?
                        sqlAndBuilder :
                        sqlAndBuilder.Append(_iSQLNodeRepository.GetAddCondition("IMPORT_TEMPLATE_ID", info.TemplateId, DBOperationString._Equal));
            sql = sql + (sqlAndBuilder.Length > 0 ? _iSQLNodeRepository.GetAndString(sqlAndBuilder, false) : "");

            var result = new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Query(sql);
            if (result.Count() != 1)
            {
                return FailMessageStatus("参数错误!");
            }
            new DataRepository<BAS_IMPORT_TEMPLATE>(_dataContext).Remove("IMPORT_TEMPLATE_ID", result.FirstOrDefault().IMPORT_TEMPLATE_ID.ToString());
            new DataRepository<BAS_IMPORT_TEMPLATE_DTL>(_dataContext).Remove("IMPORT_TEMPLATE_ID", result.FirstOrDefault().IMPORT_TEMPLATE_ID.ToString());
            return SuccessStatus();
        }
    }
}