using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;
using System.Web.Mvc;
using Abp.Application.Services.Dto;
using Abp.Auditing;
using Abp.Authorization;
using Abp.Domain.Repositories;
using Abp.Runtime.Caching;
using Abp.UI;
using IwbZero.Auditing;
using IwbZero.AppServiceBase;
using IwbZero.ToolCommon.FileHelpers;
using IwbZero.ToolCommon.Lambda;
using IwbZero.ToolCommon.LogHelpers;
using IwbZero.ToolCommon.StringModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using WeEngine.Enum;
using WePlatform.Authorization;
using WePlatform.WeBase;
using WePlatform.WeBase.SceneCategory.Dto;
using WePlatform.WeLib.Scene.Dto;

namespace WePlatform.WeLib.Scene
{
    [AbpAuthorize, AuditLog("情景库管理")]
    public class SceneAppService : IwbAsyncCrudAppService<SceneInfo, SceneDto, string, IwbPagedRequestDto, SceneCreateDto, SceneUpdateDto>, ISceneAppService
    {
        public SceneAppService(
            ICacheManager cacheManager,
            IRepository<SceneInfo, string> repository, IRepository<SceneBehaviorInfo> sbRepository, IRepository<EnvironResourceRelatedInfo> errRepository, IRepository<GuideRelatedInfo> grRepository, IRepository<SceneCategoryInfo, string> scRepository, IRepository<BehaviorInfo, string> behaviorRepository, IRepository<RoleRelateCategoryInfo> rcRepository, IRepository<BehaviorRelateRoleInfo> brrRepository) : base(repository, "Id")
        {
            SbRepository = sbRepository;
            ErrRepository = errRepository;
            GrRepository = grRepository;
            ScRepository = scRepository;
            BehaviorRepository = behaviorRepository;
            RcRepository = rcRepository;
            BrrRepository = brrRepository;
            CacheManager = cacheManager;
        }

        protected override bool KeyIsAuto { get; set; } = false;
        protected IRepository<BehaviorInfo, string> BehaviorRepository { get; }
        protected IRepository<SceneBehaviorInfo> SbRepository { get; }
        protected IRepository<SceneCategoryInfo, string> ScRepository { get; }
        protected IRepository<EnvironResourceRelatedInfo> ErrRepository { get; }
        protected IRepository<GuideRelatedInfo> GrRepository { get; }
        protected IRepository<RoleRelateCategoryInfo> RcRepository { get; }
        protected IRepository<BehaviorRelateRoleInfo> BrrRepository { get; }

        #region GetSelect

        [DisableAuditing]
        public override async Task<List<SelectListItem>> GetSelectList()
        {
            var list = await Repository.GetAllListAsync();
            var sList = new List<SelectListItem> { new SelectListItem { Text = @"请选择...", Value = "", Selected = true } };
            foreach (var l in list)
            {
                sList.Add(new SelectListItem { Value = l.Id, Text = l.Name });
            }
            return sList;
        }

        [DisableAuditing]
        public override async Task<string> GetSelectStr()
        {
            var list = await Repository.GetAllListAsync();
            string str = "<option value=\"\" selected>请选择...</option>";
            foreach (var l in list)
            {
                str += $"<option value=\"{l.Id}\">{l.Name}</option>";
            }
            return str;
        }

        #endregion GetSelect

        #region CURD

        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgCreate)]
        public override async Task Create(SceneCreateDto input)
        {
            input.Id = await AppGuidManager.GetNextRecordIdAsync(DataLibType.SceneInfo);
            //添加关联行为
            if (input.SceneBehaviors != null && input.SceneBehaviors.Any())
            {
                foreach (var behaviorDto in input.SceneBehaviors)
                {
                    var behavior = ObjectMapper.Map<SceneBehaviorInfo>(behaviorDto);
                    behavior.SceneNo = input.Id;
                    await SbRepository.InsertAsync(behavior);
                }
            }
            //添加关联环境资源
            if (input.EnvironResourceNos.Any())
            {
                foreach (var no in input.EnvironResourceNos)
                {
                    if (no.IsEmpty())
                    {
                        continue;
                    }
                    var err = new EnvironResourceRelatedInfo()
                    {
                        EnvironResourceNo = no,
                        RelatedNo = input.Id,
                        RelatedType = DataLibType.SceneInfo.ToInt()
                    };
                    await ErrRepository.InsertAsync(err);
                }
            }
            //添加变量
            string variable = "";
            if (input.Variables != null && input.Variables.Any())
            {
                foreach (var v in input.Variables)
                {
                    variable += (variable == "" ? "" : ",") + v;
                }
                //variable = StringExtensions.IsEmpty(variable) ? "" : $"[{variable}]";
            }
            input.Variable = variable;
            //添加引导信息
            if (input.GuideNos.IsNotEmpty())
            {
                var arr = input.GuideNos.Split(',');
                foreach (var no in arr)
                {
                    if (no.IsEmpty())
                    {
                        continue;
                    }
                    var guide = new GuideRelatedInfo()
                    {
                        GuideNo = no,
                        RelatedNo = input.Id,
                        RelatedType = DataLibType.SceneInfo.ToInt()
                    };
                    await GrRepository.InsertAsync(guide);
                }
            }

            await CreateEntity(input);
        }

        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgUpdate)]
        public override async Task Update(SceneUpdateDto input)
        {
            //更新变量
            string variable = "";
            if (input.Variables != null && input.Variables.Any())
            {
                foreach (var v in input.Variables)
                {
                    variable += (variable == "" ? "" : ",") + v;
                }
                //variable = StringExtensions.IsEmpty(variable) ? "" : $"[{variable}]";
            }
            input.Variable = variable;
            var dto = await UpdateEntity(input);
            //更新情景关联行为
            {
                var sceneBehaviorNos = await SbRepository.GetAll().Where(a => a.SceneNo == dto.Id).Select(a => a.BehaviorNo).ToListAsync();
                if (input.SceneBehaviors != null && input.SceneBehaviors.Any())
                {
                    foreach (var behaviorDto in input.SceneBehaviors)
                    {
                        if (behaviorDto.BehaviorNo.IsEmpty())
                        {
                            continue;
                        }
                        if (!sceneBehaviorNos.Contains(behaviorDto.BehaviorNo))
                        {
                            //新增原集合中不存在的行为
                            var behavior = ObjectMapper.Map<SceneBehaviorInfo>(behaviorDto);
                            behavior.SceneNo = input.Id;
                            await SbRepository.InsertAsync(behavior);
                        }
                        else
                        {
                            var behavior = await SbRepository.FirstOrDefaultAsync(a => a.SceneNo == input.Id && a.BehaviorNo == behaviorDto.BehaviorNo);
                            behavior.BehaviorEvalType = behaviorDto.BehaviorEvalType;
                            behavior.BehaviorWeight = behaviorDto.BehaviorWeight;
                            await SbRepository.UpdateAsync(behavior);
                            //移除原集合中已存在的行为（剩余不在新增集合中的需删除）
                            sceneBehaviorNos.Remove(behaviorDto.BehaviorNo);
                        }
                    }
                }
                //删除多余的角色
                if (sceneBehaviorNos.Any())
                {
                    foreach (var behaviorNo in sceneBehaviorNos)
                    {
                        await SbRepository.DeleteAsync(a => a.SceneNo == dto.Id && a.BehaviorNo == behaviorNo);
                    }
                }
            }

            //更新关联环境资源
            {
                var errNos = await ErrRepository.GetAll().Where(a => a.RelatedNo == dto.Id).Select(a => a.EnvironResourceNo).ToListAsync();
                if (input.EnvironResourceNos.Any())
                {
                    foreach (var no in input.EnvironResourceNos)
                    {
                        if (no.IsEmpty())
                        {
                            continue;
                        }
                        if (!errNos.Contains(no))
                        {
                            var err = new EnvironResourceRelatedInfo()
                            {
                                EnvironResourceNo = no,
                                RelatedNo = input.Id,
                                RelatedType = DataLibType.SceneInfo.ToInt()
                            };
                            await ErrRepository.InsertAsync(err);
                        }
                        else
                        {
                            errNos.Remove(no);
                        }
                    }
                }
                //删除多余的环境资源
                if (errNos.Any())
                {
                    await ErrRepository.DeleteAsync(a => a.RelatedNo == dto.Id && errNos.Contains(a.EnvironResourceNo));
                }
            }

            //更新引导信息
            {
                var grNos = await GrRepository.GetAll().Where(a => a.RelatedNo == dto.Id).Select(a => a.GuideNo).ToListAsync();
                if (input.GuideNos.IsNotEmpty())
                {
                    var arr = input.GuideNos.Split(',');
                    foreach (var no in arr)
                    {
                        if (no.IsEmpty())
                        {
                            continue;
                        }

                        if (!grNos.Contains(no))
                        {
                            var guide = new GuideRelatedInfo()
                            {
                                GuideNo = no,
                                RelatedNo = input.Id,
                                RelatedType = DataLibType.SceneInfo.ToInt()
                            };
                            await GrRepository.InsertAsync(guide);
                        }
                        else
                        {
                            grNos.Remove(no);
                        }
                    }
                }
                //删除多余的引导信息
                if (grNos.Any())
                {
                    await GrRepository.DeleteAsync(a => a.RelatedNo == dto.Id && grNos.Contains(a.GuideNo));
                }
            }
        }

        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgDelete)]
        public override Task Delete(EntityDto<string> input)
        {
            return base.Delete(input);
        }

        [DisableAuditing]
        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgQuery)]
        public override async Task<PagedResultDto<SceneDto>> GetAll(IwbPagedRequestDto input)
        {
            var query = CreateFilteredQuery(input);
            var totalCount = await AsyncQueryableExecuter.CountAsync(query);
            query = ApplySorting(query, input);
            query = ApplyPaging(query, input);
            var entities = await AsyncQueryableExecuter.ToListAsync(query);
            var dtoList = new PagedResultDto<SceneDto>(totalCount, entities.Select(MapToEntityDto).ToList());
            return dtoList;
        }

        #region GetEntity/Dto

        /// <summary>
        ///  查询实体Dto
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [DisableAuditing]
        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgQuery)]
        public override async Task<SceneDto> GetDto(EntityDto<string> input)
        {
            var entity = await GetEntity(input);
            return MapToEntityDto(entity);
        }

        /// <summary>
        ///  查询实体Dto
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [DisableAuditing]
        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgQuery)]
        public override async Task<SceneDto> GetDtoById(string id)
        {
            var entity = await GetEntityById(id);
            return MapToEntityDto(entity);
        }

        /// <summary>
        /// 查询实体Dto（需指明自定义字段）
        /// </summary>
        /// <param name="no"></param>
        /// <returns></returns>
        [DisableAuditing]
        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgQuery)]
        public override async Task<SceneDto> GetDtoByNo(string no)
        {
            var entity = await GetEntityByNo(no);
            return MapToEntityDto(entity);
        }

        /// <summary>
        /// 查询实体
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [DisableAuditing]
        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgQuery)]
        public override async Task<SceneInfo> GetEntity(EntityDto<string> input)
        {
            var entity = await GetEntityById(input.Id);
            return entity;
        }

        /// <summary>
        /// 查询实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [DisableAuditing]
        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgQuery)]
        public override async Task<SceneInfo> GetEntityById(string id)
        {
            return await Repository.FirstOrDefaultAsync(a => a.Id == id);
        }

        /// <summary>
        /// 查询实体（需指明自定义字段）
        /// </summary>
        /// <param name="no"></param>
        /// <returns></returns>
        [DisableAuditing]
        [AbpAuthorize(PermissionNames.PagesResourceMgSceneMgQuery)]
        public override async Task<SceneInfo> GetEntityByNo(string no)
        {
            //CheckGetPermission();
            if (string.IsNullOrEmpty(KeyFiledName))
            {
                ThrowError("NoKeyFieldName");
            }
            return await base.GetEntityByNo(no);
        }

        #endregion GetEntity/Dto

        #region Hide

        /// <summary>
        /// 根据给定的<see cref="IwbPagedRequestDto"/>创建 <see cref="IQueryable{SceneFlowInfo}"/>过滤查询.
        /// </summary>
        /// <param name="input">The input.</param>
        protected override IQueryable<SceneInfo> CreateFilteredQuery(IwbPagedRequestDto input)
        {
            var query = Repository.GetAllIncluding(a => a.SceneCategoryInfo);
            var pagedInput = input as IIwbPagedRequest;
            if (pagedInput == null)
            {
                return query;
            }
            if (!string.IsNullOrEmpty(pagedInput.KeyWords))
            {
                object keyWords = pagedInput.KeyWords;
                LambdaObject obj = new LambdaObject()
                {
                    FieldType = (LambdaFieldType)pagedInput.FieldType,
                    FieldName = pagedInput.KeyField,
                    FieldValue = keyWords,
                    ExpType = (LambdaExpType)pagedInput.ExpType
                };
                var exp = obj.GetExp<SceneInfo>();
                query = exp != null ? query.Where(exp) : query;
            }
            if (pagedInput.SearchList != null && pagedInput.SearchList.Count > 0)
            {
                List<LambdaObject> objList = new List<LambdaObject>();
                foreach (var o in pagedInput.SearchList)
                {
                    if (string.IsNullOrEmpty(o.KeyWords))
                        continue;
                    if (o.KeyField.ToLower() == "sceneCategory".ToLower())
                    {
                        query = query.Where(a => a.SceneCategoryInfo.CategoryPath.Contains(o.KeyWords));
                        continue;
                    }
                    object keyWords = o.KeyWords;
                    objList.Add(new LambdaObject
                    {
                        FieldType = (LambdaFieldType)o.FieldType,
                        FieldName = o.KeyField,
                        FieldValue = keyWords,
                        ExpType = (LambdaExpType)o.ExpType
                    });
                }
                var exp = objList.GetExp<SceneInfo>();
                query = exp != null ? query.Where(exp) : query;
            }
            return query;
        }

        //protected override IQueryable<SceneInfo> ApplySorting(IQueryable<SceneInfo> query, IwbPagedRequestDto input)
        //{
        //    if (input.Sorting.IsNotEmpty())
        //    {
        //        return query.OrderBy(input.Sorting);
        //    }

        //    return base.ApplySorting(query, input);
        //}

        //protected override IQueryable<SceneInfo> ApplyPaging(IQueryable<SceneInfo> query, IwbPagedRequestDto input)
        //{
        //    if (input is IPagedResultRequest pagedInput)
        //    {
        //        return query.Skip(pagedInput.SkipCount).Take(pagedInput.MaxResultCount);
        //    }
        //    return query;
        //}

        #endregion Hide

        #endregion CURD

        /// <summary>
        /// 查询情景的引导信息
        /// </summary>
        /// <param name="no"></param>
        /// <returns></returns>
        [DisableAuditing]
        public async Task<List<string>> GetSceneGuideByNo(string no)
        {
            var list = new List<string>();
            var type = DataLibType.SceneInfo.ToInt();
            var guides = await GrRepository.GetAllIncluding(a => a.GuideInfo).Where(a => a.RelatedNo == no && a.RelatedType == type).ToListAsync();
            list.Add(string.Join(",", guides.Select(a => a.GuideNo).ToArray()));
            list.Add(string.Join(",", guides.Select(a => a.GuideInfo.Name).ToArray()));
            return list;
        }

        /// <summary>
        /// 查询情景的行为信息
        /// </summary>
        /// <param name="no"></param>
        /// <returns></returns>
        [DisableAuditing]
        public async Task<List<SceneBehaviorDto>> GetSceneBehavior(string no)
        {
            var sbs = await SbRepository.GetAllListAsync(a => a.SceneNo == no);
            return sbs.Select(ObjectMapper.Map<SceneBehaviorDto>).ToList();
        }

        /// <summary>
        /// 查询情景的环境资源信息
        /// </summary>
        /// <param name="no"></param>
        /// <returns></returns>
        [DisableAuditing]
        public async Task<List<string>> GetSceneEnvironResourceNos(string no)
        {
            var type = DataLibType.SceneInfo.ToInt();
            var errs = await ErrRepository.GetAllListAsync(a => a.RelatedNo == no && a.RelatedType == type);
            return errs.Select(a => a.EnvironResourceNo).ToList();
        }

        /// <summary>
        /// 查询情景变量
        /// </summary>
        /// <param name="no"></param>
        /// <returns></returns>
        [DisableAuditing]
        public async Task<List<VariableDto>> GetSceneVariable(string no)
        {
            var variables = (await Repository.FirstOrDefaultAsync(a => a.Id == no))?.Variable ?? "";
            var variableArr = variables.StrToArray();
            var variableList = new List<VariableDto>();
            foreach (var v in variableArr)
            {
                variableList.Add(new VariableDto(v));
            }

            return variableList;
        }

        #region Import

        private string Password => ConfigurationManager.AppSettings["TEMPLATE.EXCEL.SCENE.CHECK.NO"] ?? "Iwb_Scene_V1.0.0";

        /// <summary>
        /// 导入情景
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [AbpAuthorize(), AuditLog("导入情景")]
        public async Task Import(UploadSceneDto input)
        {
            if (input.ExcelInfo.IsEmpty())
            {
                CheckErrors("未接收到上传文件，请检查后再试！");
            }

            if (input.ExcelExt != "xlsx")
            {
                CheckErrors("模板异常，请使用正确的模板填充数据后再导入。");
            }

            string filePath = $"Upload/Excel/Scene/{DateTime.Now:yyyyMM}",
                fileName = $"{DateTime.Now:ddHHmmssff}-{input.ExcelName}";
            await AttachFileManager.FileUpload(input.ExcelInfo, filePath, fileName, input.ExcelExt);
            var work = ExcelHelper.CreateWorkBook07($"{AppDomain.CurrentDomain.BaseDirectory }{filePath}/{fileName}.{input.ExcelExt}");
            var sheet = work.GetSheet("Scene");
            if (sheet == null)
            {
                CheckErrors("模板异常，请使用最新的模板填充数据后再导入。(从系统中下载最新模板)");
                return;
            }

            if (sheet.GenerateCell(1, 27).StringCellValue != Password)
            {
                CheckErrors("模板异常，请使用最新的模板填充数据后再导入(从系统中下载最新模板)。");
                return;
            }

            int rowIndex = 3;
            if (sheet.LastRowNum > 10000)
            {
                CheckErrors("导入数量太大，请分批导入。（如果记录条数小于10000，请检查空白行的格式）");
                return;
            }
            while (rowIndex <= sheet.LastRowNum)
            {
                rowIndex = await ImportScene(sheet, rowIndex);
            }
        }

        /// <summary>
        /// 导入情景
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private async Task<int> ImportScene(ISheet sheet, int rowIndex)
        {
            var row = sheet.GenerateRow(rowIndex);
            var name = row.GenerateCell(2).StringCellValue ?? "";
            if (name.IsEmpty())
            {
                rowIndex++;
                return rowIndex;
            }
            var sceneNo = await SaveScene(row);
            sheet.IsMergeCell(row.RowNum, 0, out var dimension);
            List<string> bNos = await SbRepository.GetAll().Where(a => a.SceneNo == sceneNo).Select(a => a.BehaviorNo).ToListAsync();
            for (int i = 0; i < dimension.RowSpan; i++)
            {
                var bRow = sheet.GenerateRow(rowIndex);
                var bNo = await SaveBehavior(bRow, sceneNo);
                if (bNos.Contains(bNo))
                {
                    bNos.Remove(bNo);
                }
                rowIndex++;
            }
            if (bNos.Count > 0)
            {
                await SbRepository.DeleteAsync(a => a.SceneNo == sceneNo && bNos.Contains(a.BehaviorNo));
            }
            return rowIndex;
        }

        /// <summary>
        /// 保存情景
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        private async Task<string> SaveScene(IRow row)
        {
            int columnIndex = 1;
            try
            {
                string id = row.GenerateCell(columnIndex).StringCellValue ?? "";
                SceneInfo scene = (id.IsEmpty() ? null : await Repository.FirstOrDefaultAsync(a => a.Id == id));
                columnIndex++;
                var name = row.GenerateCell(columnIndex).StringCellValue ?? "";
                if (name.Length > BehaviorInfo.NameLength)
                {
                    name = name.Substring(0, BehaviorInfo.NameLength);
                }
                if (scene == null)
                {
                    scene = new SceneInfo
                    {
                        Id = await AppGuidManager.GetNextRecordIdAsync(DataLibType.SceneInfo),
                        Name = name
                    };
                    columnIndex++;
                    scene.SceneCategory = GetTypeNo(row.GenerateCell(columnIndex).StringCellValue ?? "");
                    columnIndex++;
                    scene.SceneTag = row.GenerateCell(columnIndex).StringCellValue ?? "";
                    columnIndex++;
                    scene.Description = row.GenerateCell(columnIndex).StringCellValue ?? "";
                    columnIndex++;
                    scene.Variable = CheckString(row.GenerateCell(columnIndex).StringCellValue ?? "");
                    scene.SceneType = (int)SceneType.Evolution;
                    await Repository.InsertAndGetIdAsync(scene);
                }
                else
                {
                    scene.Name = name;
                    columnIndex++;
                    scene.SceneCategory = GetTypeNo(row.GenerateCell(columnIndex).StringCellValue ?? $"({ scene.SceneCategory})");
                    columnIndex++;
                    scene.SceneTag = row.GenerateCell(columnIndex).StringCellValue ?? "";
                    columnIndex++;
                    scene.Description = row.GenerateCell(columnIndex).StringCellValue ?? "";
                    columnIndex++;
                    scene.Variable = CheckString(row.GenerateCell(columnIndex).StringCellValue ?? "");

                    await Repository.UpdateAsync(scene);
                }
                return scene.Id;
            }
            catch (Exception e)
            {
                this.LogError(e);
                throw new UserFriendlyException($"[{(columnIndex - 1).ConvertColumnName()}{row.RowNum + 1}]内容出错，请检查后再试。");
            }
        }

        /// <summary>
        /// 保存行为
        /// </summary>
        /// <param name="row"></param>
        /// <param name="sceneNo"></param>
        /// <returns></returns>
        private async Task<string> SaveBehavior(IRow row, string sceneNo)
        {
            int columnIndex = 7;
            try
            {
                string id = row.GenerateCell(columnIndex).StringCellValue ?? "";
                BehaviorInfo behavior =
                    (id.IsEmpty() ? null : await BehaviorRepository.FirstOrDefaultAsync(a => a.Id == id));

                columnIndex = 8;
                var name = row.GenerateCell(columnIndex).StringCellValue ?? "";
                if (name.Length > BehaviorInfo.NameLength)
                {
                    name = name.Substring(0, BehaviorInfo.NameLength);
                }
                var description = row.GenerateCell(11).StringCellValue ?? "";
                if (behavior == null)
                {
                    behavior = await BehaviorRepository.FirstOrDefaultAsync(a =>
                        a.BehaviorName == name);
                    if (behavior == null)
                    {
                        behavior = new BehaviorInfo
                        {
                            Id = await AppGuidManager.GetNextRecordIdAsync(DataLibType.BehaviorInfo),
                            BehaviorName = name
                        };
                        columnIndex++;
                        behavior.SceneCategory = GetTypeNo(row.GenerateCell(columnIndex).StringCellValue ?? "");
                        columnIndex++;
                        var behaviorTag = row.GenerateCell(columnIndex).StringCellValue ?? "";
                        behaviorTag= behaviorTag.Trim().Replace("，",",");
                        while (behaviorTag.Contains(",,"))
                        {
                            behaviorTag= behaviorTag.Trim().Replace(",,",",");
                        }
                        behavior.BehaviorTag = behaviorTag;
                        columnIndex++;
                        behavior.Description = description;
                        columnIndex++;
                        columnIndex++;
                        behavior.RuleKeyword = CheckString(row.GenerateCell(columnIndex).StringCellValue ?? "");
                        behavior.RoleLogicType = (int)BehaviorRoleLogicType.Or;
                        await BehaviorRepository.InsertAndGetIdAsync(behavior);
                        await CurrentUnitOfWork.SaveChangesAsync();
                    }
                }
                else
                {
                    behavior.BehaviorName = name;
                    columnIndex++;
                    behavior.SceneCategory = GetTypeNo(row.GenerateCell(columnIndex).StringCellValue ?? $"({ behavior.SceneCategory})");
                    columnIndex++;
                    behavior.BehaviorTag = row.GenerateCell(columnIndex).StringCellValue ?? "";
                    columnIndex++;
                    behavior.Description = description;
                    columnIndex++;
                    columnIndex++;
                    behavior.RuleKeyword = CheckString(row.GenerateCell(columnIndex).StringCellValue ?? "");
                    await BehaviorRepository.UpdateAsync(behavior);
                }

                columnIndex = 12;
                string roleNos = row.GenerateCell(columnIndex).StringCellValue ?? "";
                if (roleNos.IsNotEmpty())
                {
                    await SaveBehaviorRoles(behavior.Id, roleNos);
                }

                #region 关联情景

                columnIndex = 14;
                int behaviorEvalType = GetEvalType(row.GenerateCell(columnIndex).StringCellValue ?? "");
                columnIndex++;
                decimal? behaviorWeight = null;
                try
                {
                    behaviorWeight = (decimal)row.GenerateCell(columnIndex).NumericCellValue;
                }
                catch
                {
                    var behaviorWeightStr = row.GenerateCell(columnIndex).StringCellValue;
                    if (int.TryParse(behaviorWeightStr, out var behaviorWeight2))
                    {
                        behaviorWeight = behaviorWeight2;
                    }
                }

                if (behaviorWeight != null)
                {
                    await SaveSceneBehavior(sceneNo, behavior.Id, behaviorEvalType, (decimal)behaviorWeight);
                }

                #endregion 关联情景

                return behavior.Id;
            }
            catch (Exception e)
            {
                this.LogError(e);
                throw new UserFriendlyException($"[{(columnIndex - 1).ConvertColumnName()}{row.RowNum + 1}]内容出错，请检查后再试。");
            }
        }

        /// <summary>
        /// 保存行为的角色
        /// </summary>
        /// <param name="behaviorNo"></param>
        /// <param name="roleNos"></param>
        /// <returns></returns>
        private async Task SaveBehaviorRoles(string behaviorNo, string roleNos)
        {
            roleNos = roleNos.Replace("，", ",");
            var oldNos = await BrrRepository.GetAll().Where(a => a.BehaviorNo == behaviorNo).Select(a => a.RoleNo)
                .ToListAsync();
            List<string> roleNoList = roleNos.Split(',').ToList();
            foreach (var roleNo in roleNoList)
            {
                string no = GetTypeNo(roleNo);
                await SaveBehaviorRole(behaviorNo, no);
                if (oldNos.Contains(no))
                {
                    oldNos.Remove(no);
                }
            }

            if (oldNos.Any())
            {
                await BrrRepository.DeleteAsync(a => oldNos.Contains(a.RoleNo));
            }
        }

        /// <summary>
        /// 保存行为的角色
        /// </summary>
        /// <param name="behaviorNo"></param>
        /// <param name="roleNo"></param>
        /// <returns></returns>
        private async Task SaveBehaviorRole(string behaviorNo, string roleNo)
        {
            var br = await BrrRepository.FirstOrDefaultAsync(a => a.BehaviorNo == behaviorNo && a.RoleNo == roleNo);
            if (br == null)
            {
                br = new BehaviorRelateRoleInfo()
                {
                    BehaviorNo = behaviorNo,
                    RoleNo = roleNo
                };
                await BrrRepository.InsertAsync(br);
            }
            else
            {
                br.RoleNo = roleNo;
                await BrrRepository.UpdateAsync(br);
            }
        }

        /// <summary>
        /// 行为关联情景
        /// </summary>
        /// <param name="sceneNo"></param>
        /// <param name="behaviorNo"></param>
        /// <param name="behaviorEvalType"></param>
        /// <param name="behaviorWeight"></param>
        /// <returns></returns>
        private async Task SaveSceneBehavior(string sceneNo, string behaviorNo, int behaviorEvalType, decimal behaviorWeight)
        {
            var sb = await SbRepository.FirstOrDefaultAsync(
                    a => a.SceneNo == sceneNo && a.BehaviorNo == behaviorNo);
            if (sb == null)
            {
                await SbRepository.InsertAsync(new SceneBehaviorInfo()
                {
                    BehaviorNo = behaviorNo,
                    SceneNo = sceneNo,
                    BehaviorEvalType = behaviorEvalType,
                    BehaviorWeight = behaviorWeight
                });
            }
            else
            {
                sb.BehaviorEvalType = behaviorEvalType;
                sb.BehaviorWeight = behaviorWeight;
                await SbRepository.UpdateAsync(sb);
            }
        }

        private string CheckString(string str)
        {
            return str.Replace("，", ",")
                .Replace("：", ":")
                .Replace("【", "[")
                .Replace("】", "]");
        }

        /// <summary>
        /// 提取括号内的编码
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private string GetTypeNo(string type)
        {
            var str = type.SubstringSingle(@"\(", @"\)");
            return str;
        }

        private int GetEvalType(string type)
        {
            if (Enum.TryParse(type, out BehaviorScoreType t))
            {
                return t.ToInt();
            }

            return 0;
        }

        #endregion Import

        #region Export

        /// <summary>
        /// 导出情景模板
        /// </summary>
        /// <returns></returns>
        [AbpAuthorize(), AuditLog("导出情景模板")]
        public async Task<string> ExportTemplate()
        {
            string path = AppDomain.CurrentDomain.BaseDirectory + "Resources/Template/Scene.xlsx";

            var work = ExcelHelper.CreateWorkBook07(path);
            var sheet = work.GetSheet("Scene");

            await InitExcel(sheet, work);

            var savePath = "Download/Excel/Scene";
            var fileName = "SceneTemplate.xlsx";
            var result = work.SaveWorkBook($"{AppDomain.CurrentDomain.BaseDirectory}{savePath}", fileName);
            if (!result.IsEmpty())
            {
                return null;
            }
            return $"/{savePath}/{fileName}";
        }

        /// <summary>
        /// 导出情景
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [AbpAuthorize(), AuditLog("导出情景")]
        public async Task<string> Export(IwbPagedRequestDto input)
        {
            var query = CreateFilteredQuery(input);
            query = query.OrderBy(a => a.Id);
            var entities = await AsyncQueryableExecuter.ToListAsync(query);
            if (entities.Count <= 0)
            {
                CheckErrors("没有可导出的数据，请重置搜索条件！");
                return "";
            }
            string path = AppDomain.CurrentDomain.BaseDirectory + "Resources/Template/Scene.xlsx";

            var work = ExcelHelper.CreateWorkBook07(path);
            var sheet = work.GetSheet("Scene");

            int index = 3, count = entities.Count;
            if (count > 1)
            {
                sheet.InsertRows(index, count - 1);
            }

            foreach (var entity in entities)
            {
                ExportScene(sheet, entity, ref index);
            }
            await InitExcel(sheet, work);
            var savePath = "Download/Excel/Scene";
            var fileName = $"Scene-{DateTime.Now:yyMMddHHmmss}.xlsx";
            var result = work.SaveWorkBook($"{AppDomain.CurrentDomain.BaseDirectory}{savePath}", fileName);
            if (!result.IsEmpty())
            {
                return null;
            }
            return $"/{savePath}/{fileName}";
        }

        public ISheet ExportScene(ISheet sheet, SceneInfo scene, ref int rowIndex)
        {
            int columnIndex = 1;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(scene.Id ?? "");
            //sheet.SetCellPatriarch07(sheet.GenerateCell(rowIndex, columnIndex).SetValue(scene.Id ?? ""), "情景编码为空会自动创建，不为空则覆盖系统内同编号的数据，请谨慎修改。");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(scene.Name ?? "");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue($"{scene.SceneCategoryName}\r\n({scene.SceneCategory})");
            //sheet.SetCellDropdownList07(sheet.GenerateCell(rowIndex, columnIndex).SetValue($"{scene.SceneCategoryName}({scene.SceneCategory})"), categoryNames);
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(scene.SceneTag ?? "");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(scene.Description ?? "");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(scene.Variable ?? "");
            var behaviors = SbRepository.GetAllIncluding(a => a.BehaviorInfo).Where(a => a.SceneNo == scene.Id).ToList();
            int count = behaviors.Count;
            if (count > 1)
            {
                sheet.InsertRows(rowIndex, count - 1);
                for (int i = 1; i <= columnIndex; i++)
                {
                    var cellRegion = rowIndex.GetCellRegion(rowIndex + count - 1, i, i);
                    sheet.AddMergedRegion(cellRegion);
                }
            }
            if (count > 0)
            {
                foreach (var behavior in behaviors)
                {
                    sheet = ExportBehavior(sheet, behavior, columnIndex, ref rowIndex);
                    rowIndex++;
                }
            }
            else
            {
                rowIndex++;
            }
            return sheet;
        }

        public ISheet ExportBehavior(ISheet sheet, SceneBehaviorInfo sb, int columnIndex, ref int rowIndex)
        {
            BehaviorInfo behavior = sb.BehaviorInfo;
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(behavior?.Id ?? "");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(behavior?.BehaviorName ?? "");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue($"{behavior?.SceneCategoryName}\r\n({behavior?.SceneCategory})");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(behavior?.BehaviorTag ?? "");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(behavior?.Description ?? "");
            columnIndex++;
            var roles = BrrRepository.GetAllIncluding(a => a.RoleInfo).Where(a => a.BehaviorNo == behavior.Id)
                .Select(a => a.RoleInfo.RoleName + "(" + a.RoleNo + ")").ToArray();
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(roles.Length > 0 ? string.Join(",", roles) : "");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(behavior?.RuleKeyword ?? "");
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue(Enum.GetName(typeof(BehaviorScoreType), sb.BehaviorEvalType));
            columnIndex++;
            sheet.GenerateCell(rowIndex, columnIndex).SetValue<decimal>(sb.BehaviorWeight);

            return sheet;
        }

        private async Task InitExcel(ISheet sheet, XSSFWorkbook work)
        {
            //var startRow = 1;
            sheet.GenerateCell(1, 27).SetCellValue(Password);
            var dataSheet = work.CreateSheet($"DATA{DateTime.Now:HHmmssff}");
            work.SetSheetHidden(work.GetSheetIndex(dataSheet.SheetName), true);
            var categoryNames = await GetCategoryNames();
            var data = await GetCategoryRoles(categoryNames);
            var index = 0;
            dataSheet.BuildListData(data, ref index);
            var r1 = 3.GetCellRegion(65536, 3, 3);
            var r2 = 3.GetCellRegion(65536, 9, 9);
            var c1 = sheet.CreateValidationConstraint(data.Name);
            sheet.SetCellDropdownList07(c1, r1, "请选择情景的场景类别...", "情景场景类别");
            sheet.SetCellDropdownList07(c1, r2, "请选择行为的场景类别...", "行为场景类别");
            var r3 = 3.GetCellRegion(65536, 12, 12);
            var flag = "INDIRECT(ADDRESS(ROW(),COLUMN()-3))";
            var formula = $"MID({flag}, FIND(\"(\", {flag}) + 1, FIND(\")\",{flag}) - FIND(\"(\", {flag}) - 1)";
            var c2 = sheet.CreateLinkValidationConstraint(formula);
            sheet.SetCellDropdownList07(c2, r3, "请在下拉框里选择行为的处理角色... \r\n(需要先选择行为的场景类别)", "行为角色");
            sheet.SetCellDropdownList07(14, 14, Enum.GetNames(typeof(BehaviorScoreType)), "请选择情景行为类型... \r\nNormal：正向行为(加分) \r\nNegative：负向行为(减分)  \r\nImportantNegative：关键性负向行为（0分）", 2, "情景行为类型");

            //sheet.SetColumnMsg07("编码为空会自动创建，不为空则覆盖系统内同编号的数据，请谨慎修改。", 1, 1, startRow, "情景编码");
            //sheet.SetColumnMsg07("请在下拉框里选择情景的场景类别...", 3, 3, startRow, "情景场景类别");
            //sheet.SetCellDropdownList07(work, 3, 3, categoryNames, "请在下拉框里选择情景的场景类别...", startRow + 1, "情景场景类别");
            //sheet.SetColumnMsg07("描述中的变量 以\"@\"开头，且需要在情景变量中配置默认值！", 5, 5, startRow, "情景描述");
            //sheet.SetColumnMsg07("变量的格式为[@变量名:变量值类型:变量默认值] \r\n变量值类型有：string,decimal,date \r\n多个变量用,分隔（英文字符,） \r\n 例：\"[@Address:string:四川北路],[@Num:decimal:10],[@Date:date:2020-09-01]\"", 6, 6, startRow, "情景变量");
            //sheet.SetColumnMsg07("编码为空会自动创建，不为空则覆盖系统内同编号的数据，请谨慎修改。", 7, 7, startRow, "行为编码");
            //sheet.SetCellDropdownList07(work, 9, 9, categoryNames, "请在下拉框里选择行为的场景类别...", startRow + 1, "行为场景类别");
            //sheet.SetColumnMsg07("关键字格式\r\n[[关键字1,关键字2]:比重,[关键字3,关键字4]:比重] \r\n例：\"[[123,345]:60,[234,456]:70]\"", 12, 12, startRow, "行为关键字");

            //sheet.SetColumnMsg07("行为在情景中所占的比重\r\n", 14, 14, startRow, "行为权重");
            //sheet.SetCellMinNumeric07(14, 0, "行为在情景中所占的比重\r\n", startRow, "行为权重");
        }

        private async Task<string[]> GetCategoryNames()
        {
            List<string> sList = new List<string>();
            var scList = await ScRepository.GetAll().Where(a => a.ParentNo != null).OrderBy(a => a.CategoryPath).Select(a => new SceneCategoryDto()
            {
                CategoryName = a.CategoryName,
                Id = a.Id
            }).ToListAsync();
            foreach (var s in scList)
            {
                sList.Add($"{s.CategoryName}\r\n({s.Id})");
            }

            return sList.ToArray();
        }

        private async Task<DropdownListData> GetCategoryRoles(string[] categoryNames)
        {
            var data = new DropdownListData("CategoryRoles");

            foreach (var categoryName in categoryNames)
            {
                var cData = new DropdownListData(GetTypeNo(categoryName), categoryName);
                var no = GetTypeNo(categoryName);
                var roleList = await RcRepository.GetAllIncluding(a => a.RoleInfo).Where(a => a.CategoryNo == no)
                    .ToListAsync();
                var roles = roleList.Select(a => new { a.RoleNo, a.RoleName });
                foreach (var role in roles)
                {
                    cData.AddList(new DropdownListData(role.RoleNo, $"{role.RoleName}({role.RoleNo})"));
                }
                data.AddList(cData);
            }

            return data;
        }

        #endregion Export
    }
}