OA2023/OASystem/OASystem.Api/OAMethodLib/GeneralMethod.cs


using Aspose.Cells;
using Aspose.Words;
using Aspose.Words.Layout;
using Aspose.Words.Saving;
using Aspose.Words.Tables;
using Dm.util;
using Microsoft.AspNetCore.SignalR;
using Microsoft.International.Converters.PinYinConverter;
using NodaTime;
using NPOI.OpenXmlFormats.Vml;
using OASystem.API.OAMethodLib.File;
using OASystem.API.OAMethodLib.Hub.HubClients;
using OASystem.API.OAMethodLib.Hub.Hubs;
using OASystem.API.OAMethodLib.JuHeAPI;
using OASystem.API.OAMethodLib.KiMiApi;
using OASystem.API.OAMethodLib.SignalR.Hubs;
using OASystem.Domain.AesEncryption;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Domain.ViewModels.JuHeExchangeRate;
using OASystem.Domain.ViewModels.Statistics;
using OASystem.Infrastructure.Repositories.CRM;
using OASystem.Infrastructure.Repositories.Groups;
using System.Data;
using System.IdentityModel.Tokens.Jwt;
using System.Security.Claims;

namespace OASystem.API.OAMethodLib
{
    public static class GeneralMethod
    {
        //团组信息
        private static readonly SqlSugarClient _sqlSugar = AutofacIocManager.Instance.GetService<SqlSugarClient>();
        private static readonly DelegationInfoRepository _dirRep = AutofacIocManager.Instance.GetService<DelegationInfoRepository>();
        private static readonly TeamRateRepository _teamRateRep = AutofacIocManager.Instance.GetService<TeamRateRepository>();
        private static readonly IJuHeApiService _juHeApi = AutofacIocManager.Instance.GetService<IJuHeApiService>();
        private static readonly SetDataRepository _setDataRep = AutofacIocManager.Instance.GetService<SetDataRepository>();
        private static readonly TableOperationRecordRepository _tableOperationRecordRep = AutofacIocManager.Instance.GetService<TableOperationRecordRepository>();
        private static readonly MessageRepository _messageRep = AutofacIocManager.Instance.GetService<MessageRepository>();

        private static readonly IHubContext<ChatHub, IChatClient> _hubContext = AutofacIocManager.Instance.GetService<IHubContext<ChatHub, IChatClient>>();

        private readonly static string[] weekdays = new string[] { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };

        #region 团组操作-->团组归属部门

        /// <summary>
        /// 团组操作-->团组归属部门
        /// </summary>
        /// <returns></returns>
        public static async Task<List<GroupOpAffiliationBranchView>> GroupOpAffiliationBranchInit()
        {
            /*
                * 1.sq 和 gyy 等显示 市场部
                * 2.王鸽和主管及张总还有管理员号统一国交部
                * 2-1.  4	管理员 ,21	张海麟
                */

            //List<int> userIds = new List<int>() { 4, 21 };
            var userDepDatas = new List<GroupOpAffiliationBranchView>();
            //userDepDatas = _sqlSugar.Queryable<Sys_Users>()
            //    .LeftJoin<Sys_Department>((u, d) => u.DepId == d.Id)
            //    .Where(u => u.IsDel == 0)
            //    .Select((u, d) => new GroupOpAffiliationBranchView { value = userIds.Contains(u.Id) ? "国交部" : d.DepName, text = userIds.Contains(u.Id) ? "国交部" : d.DepName })
            //    .ToList();
            //userDepDatas.Insert(0, new GroupOpAffiliationBranchView() { text = "公司", value = "公司" });
            userDepDatas.Insert(0, new GroupOpAffiliationBranchView() { text = "全部", value = "全部" });
            userDepDatas.Insert(1, new GroupOpAffiliationBranchView() { text = "国交部", value = "国交部" });
            userDepDatas.Insert(2, new GroupOpAffiliationBranchView() { text = "市场部", value = "市场部" });
            userDepDatas.Insert(3, new GroupOpAffiliationBranchView() { text = "策划部", value = "策划部" });

            return userDepDatas;
        }

        public class GroupOpAffiliationBranchView
        {
            public string value { get; set; }
            public string text { get; set; }
        }

        #endregion

        #region 员工注册默认添加基础页面

        /// <summary>
        /// 默认职位权限
        /// </summary>
        public static bool DefaultPostAuth(int depId, int postId, int userId, int createUserId)
        {
            if (depId < 1) return false;
            if (postId < 1) return false;
            if (userId < 1) return false;
            if (createUserId < 1) return false;

            List<DefaultPostPageAuthView> _defaultData = AppSettingsHelper.Get<DefaultPostPageAuthView>("DefaultPostPageData");

            if (_defaultData.Count < 1) return false;

            var postJobDatas = _dirRep._sqlSugar.Queryable<Sys_JobPost>().Where(it => it.IsDel == 0).ToList();

            List<int> pageData = new List<int>();
            //添加公司公共页面
            pageData.AddRange(_defaultData.Find(it => it.DepId == -1)?.PostPageAuths[0].PageIds ?? new List<int>());
            //添加部门页面
            var depPublicPageData = _defaultData.Find(it => it.DepId == depId);
            var jobPublicPageData = postJobDatas.Find(it => it.DepId == depId && it.Id == postId);
            if (depPublicPageData != null) //特殊部门 
            {
                //公共页面
                pageData.AddRange(depPublicPageData.PostPageAuths.Find(it => it.PostId == -1)?.PageIds ?? new List<int>());

                if (depId == 7) //国交部特殊处理
                {
                    List<string> postNames = new List<string>() { "主管", "经理" };
                    //岗位页面
                    if (jobPublicPageData != null)
                    {

                        if (postNames.Contains(jobPublicPageData.JobName))
                        {
                            pageData.AddRange(depPublicPageData.PostPageAuths.Find(it => it.PostId == 0)?.PageIds ?? new List<int>());
                        }
                        else
                        {
                            pageData.AddRange(depPublicPageData.PostPageAuths.Find(it => it.PostId == postId)?.PageIds ?? new List<int>());
                        }
                    }
                }

                #region 按照岗位默认分配页面 特殊处理 市场部-销售顾问 

                var salesDefaultPages = new List<int>() {
                    27 , //团组操作
                    104, //接团客户名单
                    106, //倒推表
                    118, //出入境费用明细
                    168, //出入境国家三公费用标准
                    194, //出入境费用明细-草稿
                    196, //前期需求表单收集
                };

                var jobName = jobPublicPageData.JobName;
                if (!string.IsNullOrEmpty(jobName))
                {
                    if (jobPublicPageData.JobName.Equals("销售顾问"))
                    {
                        pageData.AddRange(salesDefaultPages);
                        pageData = pageData.Distinct().OrderBy(x => x).ToList();
                    }
                }
                #endregion
            }
            else //通用部门
            {
                pageData.AddRange(_defaultData.Find(it => it.DepId == 0)?.PostPageAuths[0].PageIds ?? new List<int>());
            }


            if (pageData.Count > 0)
            {
                //页面操作权限数据(添加修改等...)
                var pageFunctionData = _dirRep._sqlSugar.Queryable<Sys_SystemMenuAndFunction>().Where(it => pageData.Contains(it.SmId) && it.IsDel == 0).ToList();

                var defaultPageData = pageFunctionData.Select(it => new Sys_UserAuthority()
                {
                    UId = userId,
                    SmId = it.SmId,
                    FId = it.FId,
                    CreateUserId = createUserId, //管理员
                    CreateTime = DateTime.Now,
                    IsDel = 0,
                    IsTemp = 1
                }).ToList();


                if (defaultPageData.Count > 0)
                {
                    //移除每个页面的审核权限，审核权限需要单独开启
                    defaultPageData = defaultPageData.Where(it => it.FId != 12).ToList();

                    //添加页面操作权限
                    var s = _dirRep._sqlSugar.Fastest<Sys_UserAuthority>().PageSize(100000).BulkCopy(defaultPageData);
                    if (s > 0)
                    {
                        return true;
                    }
                }
            }
            return false;
        }

        #endregion

        #region 消息 
        /// <summary>
        /// 消息 发布And 通知
        /// </summary>
        /// <param name="msgTypeEnum">
        /// 消息类型 
        /// 1 公告通知
        /// 2 团组流程管控通知
        /// 3 团组业务操作通知
        /// 4 团组费用审核消息
        /// 5 团组签证进度通知
        /// 6 团组任务进度通知
        /// 7 日付申请通知
        /// </param>
        /// <param name="title">消息标题</param>
        /// <param name="content">消息内容</param>
        /// <param name="userIds"></param>
        /// <param name="diId">团组id</param>
        /// <param name="param">团组id</param>
        /// <returns></returns>
        public static async Task<bool> MessageIssueAndNotification(MessageTypeEnum msgTypeEnum, string title, string content, List<int> userIds, int diId = 0, string param = "")
        {

            MessageDto messageDto = new()
            {
                Type = msgTypeEnum,
                IssuerId = 4,//管理员
                DiId = diId,
                Title = title,
                Content = content,
                ReleaseTime = DateTime.Now,
                UIdList = userIds,
                Param = param
            };
            var status = await _messageRep.AddMsg(messageDto);//添加消息

            if (status)
            {
                //给在线在用户发送消息
                var connIds = UserStore.OnlineUser.Where(it => userIds.Contains(it.UserId)).Select(it => it.ConnectionId).ToList();

                var notificationTypeStr1 = GetMsgNotificationType(msgTypeEnum);
                var notificationTypeStr = JsonConvert.SerializeObject(
                       new
                       {
                           UserIds = userIds,
                           Msg = $"您有一条{notificationTypeStr1}相关的消息!"
                       }
                   );
                await _hubContext.Clients.Clients(connIds).ReceiveMessage($"您有一条{notificationTypeStr1}相关的消息!");

                return true;
            }

            return false;
        }

        /// <summary>
        /// 根据消息类型 获取 消息通知类型
        /// </summary>
        /// <returns></returns>
        public static string GetMsgNotificationType(MessageTypeEnum msgTypeEnum)
        {
            int notificationType = 0;
            string notificationStr = "";
            List<NotificationTypeView> notificationTypeViews = AppSettingsHelper.Get<NotificationTypeView>("MessageNotificationType");
            notificationType = notificationTypeViews.Where(it => it.MsgTypeIds.Contains((int)msgTypeEnum)).Select(it => it.TypeId).FirstOrDefault();

            if (notificationType == 1021) notificationStr = "操作";
            else if (notificationType == 1020) notificationStr = "任务";

            return notificationStr;
        }

        #endregion

        #region md5 加密

        /// <summary>
        /// MD5加密,和动网上的16/32位MD5加密结果相同,
        /// 使用的UTF8编码
        /// </summary>
        /// <param name="source">待加密字串</param>
        /// <param name="length">16或32值之一,其它则采用.net默认MD5加密算法</param>
        /// <returns>加密后的字串</returns>
        public static string Encrypt(string source, int length = 32)
        {
            if (string.IsNullOrWhiteSpace(source))
                return string.Empty;
            HashAlgorithm hashAlgorithm = CryptoConfig.CreateFromName("MD5") as HashAlgorithm;
            byte[] bytes = Encoding.UTF8.GetBytes(source);
            byte[] hashValue = hashAlgorithm.ComputeHash(bytes);
            StringBuilder sb = new StringBuilder();
            switch (length)
            {
                case 16://16位密文是32位密文的9到24位字符
                    for (int i = 4; i < 12; i++)
                    {
                        sb.Append(hashValue[i].ToString("x2"));
                    }
                    break;
                case 32:
                    for (int i = 0; i < 16; i++)
                    {
                        sb.Append(hashValue[i].ToString("x2"));
                    }
                    break;
                default:
                    for (int i = 0; i < hashValue.Length; i++)
                    {
                        sb.Append(hashValue[i].ToString("x2"));
                    }
                    break;
            }
            return sb.ToString();
        }


        #endregion

        #region jwt

        /// <summary>
        /// 获取token
        /// </summary>
        /// <param name="_config"></param>
        /// <param name="Number"></param>
        /// <param name="uId"></param>
        /// <param name="uName"></param>
        /// <param name="exp"></param>
        /// <returns></returns>
        public static async Task<string> GetToken(IConfiguration _config, string Number, int uId, string uName, DateTime exp)
        {
            string userId = Guid.NewGuid().ToString().Replace("-", "");
            var claims = new[] {
                new Claim(ClaimTypes.NameIdentifier, uName),
                new Claim(ClaimTypes.NameIdentifier, uId.ToString()),
                new Claim(ClaimTypes.NameIdentifier, userId),
                new Claim("Number",Number)
            };
            var key = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(_config["JwtSecurityKey"]));
            var creds = new SigningCredentials(key, SecurityAlgorithms.HmacSha256);
            var token = new JwtSecurityToken(
                issuer: "OASystem.com",
                audience: "OASystem.com",
                claims: claims,
                expires: exp,
                signingCredentials: creds);

            var identity = new ClaimsIdentity(claims, "login");
            var principal = new ClaimsPrincipal(identity);

            // await _httpContext.SignInAsync (CookieAuthenticationDefaults.AuthenticationScheme, principal);



            return new JwtSecurityTokenHandler().WriteToken(token);

        }


        #endregion

        #region  数据类型转换

        /// <summary>
        /// object 转 Int
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static int GetInt(this object obj)
        {
            if (obj == null)
                return 0;
            var number = 0;
            var reslut = Int32.TryParse(obj.ToString(), out number);
            return number;

        }

        private static DateTime _dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
        private static long _longTime = 621355968000000000;
        private static int _samllTime = 10000000;

        /// <summary>
        /// 时间戳 转 datetime
        /// </summary>
        /// <param name="timeStamp"></param>
        /// <returns></returns>
        public static DateTime GetTimeSpmpToDate(this object timeStamp)
        {
            if (timeStamp == null) return _dateStart;
            DateTime dateTime = new DateTime(_longTime + Convert.ToInt64(timeStamp) * _samllTime, DateTimeKind.Utc).ToLocalTime();
            return dateTime;
        }

        #endregion

        #region 用户页面操作功能 权限

        /// <summary>
        /// 用户页面操作功能(可使用)
        /// </summary>
        /// <param name="userId">用户Id</param>
        /// <param name="PageId">页面Id</param>
        /// <returns></returns>
        public static async Task<PageFunAuthViewBase> PostUserPageFuncDatas(int userId, int PageId)
        {
            PageFunAuthViewBase pageFunAuth = new PageFunAuthViewBase();

            List<UserPageFuncView> userPageFuncDatas = new List<UserPageFuncView>();
            string sql = string.Format(@"Select ua.UId As UserId, u.CnName As UserName, pa.ModuleId,pa.ModuleName,pa.PageId,pa.PageName,pa.PageIsEnable,
					                     pa.PagePhoneIsEnable,pa.FuncId,pa.FuncName,pa.FuncIsEnable
					                     From Sys_UserAuthority ua
					                     Left Join Sys_Users u On ua.UId = u.Id
					                     Left Join (
						                     Select sd.Id As ModuleId,sd.Name As ModuleName, smp.Id As PageId,smp.Name As PageName,smp.IsEnable As PageIsEnable,
						                     smp.phoneIsEnable As PagePhoneIsEnable,pfp.Id As FuncId,pfp.FunctionName As FuncName,pfp.IsEnable As FuncIsEnable
						                     From  Sys_SystemMenuAndFunction smaf
						                     Left Join Sys_SystemMenuPermission smp On smaf.SmId = smp.Id
						                     Left Join Sys_SetData sd On sd.STid = 5 And smp.Mid = sd.Id
						                     Left Join Sys_PageFunctionPermission pfp On smaf.FId = pfp.Id
						                     Where smaf.IsDel = 0 And smp.IsDel = 0 And pfp.IsDel = 0 And sd.IsDel = 0 And smp.IsEnable = 1
					                     ) As pa On ua.SmId = pa.PageId And ua.FId = pa.FuncId
					                     Where ua.IsDel = 0 And ua.UId = {0} And pa.PageId = {1}
					                     Order By ModuleId,PageId,FuncId Asc", userId, PageId);

            userPageFuncDatas = await _dirRep._sqlSugar.SqlQueryable<UserPageFuncView>(sql).ToListAsync();

            if (userPageFuncDatas.Count <= 0)
            {
                return pageFunAuth;
            }

            UserPageFuncView userPageFunc = new UserPageFuncView();

            //查询 1
            userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 1).FirstOrDefault();
            if (userPageFunc != null) pageFunAuth.CheckAuth = 1;
            //删除 2
            userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 2).FirstOrDefault();
            if (userPageFunc != null) pageFunAuth.DeleteAuth = 1;
            //编辑 3
            userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 3).FirstOrDefault();
            if (userPageFunc != null) pageFunAuth.EditAuth = 1;
            //下载 4
            userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 4).FirstOrDefault();
            if (userPageFunc != null) pageFunAuth.FilesDownloadAuth = 1;
            //上传 5
            userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 5).FirstOrDefault();
            if (userPageFunc != null) pageFunAuth.FilesUploadAuth = 1;
            //添加 11
            userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 11).FirstOrDefault();
            if (userPageFunc != null) pageFunAuth.AddAuth = 1;
            //审核 12
            userPageFunc = userPageFuncDatas.Where(it => it.FuncId == 12).FirstOrDefault();
            if (userPageFunc != null) pageFunAuth.AuditAuth = 1;

            return pageFunAuth;
        }

        #endregion

        #region 业务模块 团组权限

        /// <summary>
        /// 业务模块 团组操作权限
        /// 验证 并返回可操作的团
        /// </summary>
        /// <param name="diId">团组Id</param>
        /// <param name="userId">用户Id</param>
        /// <param name="CTable">业务模块Id</param>
        /// <returns></returns>
        public static async Task<Result> PostGroupOperationAuth(int diId, int userId, int CTable)
        {
            Result _result = new Result { Code = -1, Msg = "No Operation Authorty!" };
            if (CTable < 1)
            {
                _result.Msg = "请填写正确的用户Id!";
                return _result;
            }

            var data = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(it => it.DIId == diId && it.UId == userId && it.CTId == CTable && it.IsDel == 0).First();

            if (data == null)
            {
                _result.Msg = "你没有本团下的该业务模块操作,请联系主管分配操作权限!";
            }
            else
            {
                _result.Code = 0;
            }
            return _result;
        }


        /// <summary>
        /// 业务模块 团组操作权限
        /// 返回可操作的团
        /// </summary>
        /// <param name="userId">用户Id</param>
        /// <param name="CTable">业务模块Id</param>
        /// <returns></returns>
        public static async Task<List<Web_ShareGroupInfoView>> PostOperationAuthReturnGroupInfosAsync(int userId, int CTable)
        {
            var shareGroupInfoViews = new List<Web_ShareGroupInfoView>();
            var taskData = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(it => it.UId == userId && it.CTId == CTable && it.IsDel == 0).ToList();

            if (taskData.Count < 1) return shareGroupInfoViews;

            var diIds = string.Join(",", taskData.Select(it => it.DIId).ToList());
            var sql = string.Format(@"Select Id,TeamName,TourCode,ClientName,VisitCountry,VisitStartDate,VisitEndDate,VisitDays,VisitPNumber 
                                            From Grp_DelegationInfo Where Id In({0}) And IsDel = 0 Order By Id Desc", diIds);
            shareGroupInfoViews = await _dirRep._sqlSugar.SqlQueryable<Web_ShareGroupInfoView>(sql).ToListAsync();

            return shareGroupInfoViews;
        }

        #endregion

        #region 团组相关 

        #region 建团按国家默认添加汇率 / 默认权限分配


        /// <summary>
        /// 团组汇率 
        /// 建团时 添加基础汇率 CNY
        /// 按国家 添加 默认币种
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="diId"></param>
        /// <returns></returns>
        public static async Task<Result> PostGroupRateAddInit(int userId, int diId)
        {

            Result result = new() { Code = -2 };

            if (userId < 1)
            {
                result.Msg = string.Format(@"请传入正确的userId");
                return result;
            }

            if (diId < 1)
            {
                result.Msg = string.Format(@"请传入正确的DiId");
                return result;
            }
            //美元(USD):1.0000|欧元(EUR):1.0000|墨西哥比索(MXN):1.0000
            string rateInit = string.Format(@"人民币(CNY):1.0000");

            //var gropInfo = _dirRep._sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && it.Id == diId).First();
            //if (gropInfo == null)
            //{
            //    result.Msg = $@"未查询到团组信息!";
            //    return result;
            //}

            var juheRateData = await _juHeApi.GetExchangeRateAsync();

            if (juheRateData.Error_code != 0)
            {
                result.Msg = juheRateData.Reason;
                return result;
            }
            List<ExchangeRateModel> exchangeRateModels = (List<ExchangeRateModel>)juheRateData.Result;
            if (exchangeRateModels.Count <= 0)
            {
                result.Msg = $@"未查询到聚合接口汇率信息!";
                return result;
            }

            if (exchangeRateModels.Count > 0)
            {
                var codes = _dirRep._sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 66).ToList();
                for (int i = 0; i < exchangeRateModels.Count; i++)
                {
                    string currencyName = exchangeRateModels[i].Name;
                    string code = "";
                    var currencyData = codes.Where(it => it.Remark == currencyName).FirstOrDefault();
                    if (currencyData != null)
                    {
                        code = currencyData.Name;

                        decimal currRate = 0.00M;
                        string MSellPri = exchangeRateModels[i].MSellPri;
                        if (!string.IsNullOrEmpty(MSellPri))
                        {
                            currRate = Convert.ToDecimal(MSellPri) / 100M;
                        }

                        rateInit += $@"|{currencyName}({code}):{currRate.ToString("#0.0000")}";
                    }
                }
            }

            var cTableIds = _dirRep._sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 16).ToList();

            if (cTableIds.Count < 1)
            {
                result.Msg = $@"未查询到操作模块信息!";
                return result;
            }

            List<Grp_TeamRate> grp_TeamRates = new List<Grp_TeamRate>();

            foreach (var item in cTableIds)
            {
                grp_TeamRates.Add(
                    new Grp_TeamRate() { DiId = diId, CTable = item.Id, Remark = rateInit, CreateUserId = userId }
                    );
            }

            if (grp_TeamRates.Count > 0)
            {
                var addId = _teamRateRep._sqlSugar.Insertable(grp_TeamRates).ExecuteCommand();
                if (addId < 1)
                {

                    result.Msg = string.Format(@"添加失败!");
                    return result;
                }
            }

            result.Code = 0;
            result.Msg = string.Format(@"操作成功!");
            return result;
        }

        /// <summary>
        /// 团组任务分配 
        /// 建团时 默认按照岗位分配权限
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="diId"></param>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public static async Task<Result> PostGroupAuthAddInit(int userId, int diId, int companyId = 2)
        {

            Result result = new() { Code = -2 };

            if (userId < 0)
            {
                result.Msg = string.Format(@"请传入正确的userId");
                return result;
            }

            if (companyId < 0)
            {
                result.Msg = string.Format(@"请传入正确的companyId");
                return result;
            }

            if (diId < 0)
            {
                result.Msg = string.Format(@"请传入正确的DiId");
                return result;
            }

            var usersData = await _teamRateRep._sqlSugar.Queryable<Sys_Users>()
                .LeftJoin<Sys_Department>((su, sd) => su.DepId == sd.Id && sd.IsDel == 0)
                .LeftJoin<Sys_JobPost>((su, sd, sjp) => su.JobPostId == sjp.Id && sjp.IsDel == 0)
                .Where(su => su.IsDel == 0)
                .Select((su, sd, sjp) => new
                {
                    su.DepId,
                    sd.DepName,
                    su.JobPostId,
                    sjp.JobName,
                    su.Id,
                    su.CnName
                })
                .ToListAsync();

            /*
             * 76  酒店预订  --> 国交部门 酒店,主管 岗位
             * 77  行程  --> 国交部门 经理,主管 岗位
             * 79  车/导游地接  --> 国交部门 OP,主管 岗位
             * 80  签证  --> 国交部门 签证 岗位
             * 81  邀请/公务活动 --> 国交部门 商邀 岗位
             * 82  团组客户保险 --> 国交部 经理,主管 岗位
             * 85  机票预订  --> 国交部门 机票 岗位
             * 98  其他款项  --> 总经办部门 总经理 岗位/国交部门(ALL) 岗位/财务部门(ALL) 岗位/策划部门(ALL) 岗位/人事部门 采购 岗位
             * 1015 超支费用,69 收款退还  --> 财务部门(ALL)岗位
             * 1015 超支费用  --> 国交部(ALL)岗位
             * 1081	文档下载  -->  国交部门 酒店,主管 岗位
             */
            var groupsTaskAssignments = new List<Grp_GroupsTaskAssignment>();
            // 76  酒店预订  --> 国交部门(7) 酒店(25)、主管(22) 岗位 
            groupsTaskAssignments.AddRange(
                usersData
                .Where(it => it.DepId == 7 && (it.JobPostId == 25 || it.JobPostId == 22))
                .Select(it => new Grp_GroupsTaskAssignment()
                {
                    DIId = diId,
                    CTId = 76,
                    UId = it.Id,
                    CreateUserId = userId
                }).ToList()
            );

            // 77  行程  --> 国交部门(7) 经理(32),主管(22) 岗位
            groupsTaskAssignments.AddRange(
                usersData
                .Where(it => it.DepId == 7 && (it.JobPostId == 22 || it.JobPostId == 32))
                .Select(it => new Grp_GroupsTaskAssignment()
                {
                    DIId = diId,
                    CTId = 77,
                    UId = it.Id,
                    CreateUserId = userId
                }).ToList()
            );

            //79  车/导游地接  --> 国交部门(7) OP(28)、主管(22) 岗位
            //groupsTaskAssignments.AddRange(
            //    usersData.Where(it => it.DepId == 7 && (it.JobPostId == 22 || it.JobPostId == 28))
            //    .Select(it => new Grp_GroupsTaskAssignment()
            //    {
            //        DIId = diId,
            //        CTId = 79,
            //        UId = it.Id,
            //        CreateUserId = userId
            //    }).ToList()
            //);

            //81  邀请/公务活动 --> 国交部门(7) 商邀(27) 岗位
            //groupsTaskAssignments.AddRange(
            //    usersData.Where(it => it.DepId == 7 && it.JobPostId == 27)
            //    .Select(it => new Grp_GroupsTaskAssignment()
            //    {
            //        DIId = diId,
            //        CTId = 81,
            //        UId = it.Id,
            //        CreateUserId = userId
            //    }).ToList()
            //);

            //2024年8月26日16点27分 “邀请公务活动分配给王鸽(UserID：149)”
            //groupsTaskAssignments.Add(
            //     new Grp_GroupsTaskAssignment()
            //     {
            //         DIId = diId,
            //         CTId = 81,
            //         UId = 149,
            //         CreateUserId = userId
            //     }
            //);

            //80  签证  --> 国交部门(7) 签证(26) 岗位
            //groupsTaskAssignments.AddRange(
            //    usersData
            //    .Where(it => it.DepId == 7 && it.JobPostId == 26)
            //    .Select(it => new Grp_GroupsTaskAssignment()
            //    {
            //        DIId = diId,
            //        CTId = 80,
            //        UId = it.Id,
            //        CreateUserId = userId
            //    }).ToList()
            //);

            //82  团组客户保险 --> 国交部(7) 经理(32),主管(22) 岗位
            //2024-06-25 默认将保险权限分配给签证岗。 签证(26) 岗位
            List<int> insurancePositions = new List<int>() { 32, 22, 26 };
            groupsTaskAssignments.AddRange(
                usersData
                .Where(it => it.DepId == 7 && insurancePositions.Contains(it.JobPostId))
                .Select(it => new Grp_GroupsTaskAssignment()
                {
                    DIId = diId,
                    CTId = 82,
                    UId = it.Id,
                    CreateUserId = userId
                }).ToList()
            );

            //85  机票预订  --> 国交部门(7) 机票(24),主管(22) 岗位
            groupsTaskAssignments.AddRange(
                usersData
                .Where(it => it.DepId == 7 && (it.JobPostId == 22 || it.JobPostId == 24))
                .Select(it => new Grp_GroupsTaskAssignment()
                {
                    DIId = diId,
                    CTId = 85,
                    UId = it.Id,
                    CreateUserId = userId
                }).ToList()
            );


            //98  其他款项  --> 总经办部门(1) 总经理(1) 岗位/国交部门(7)(ALL) 岗位/财务部门(3)(ALL) 岗位/策划部门(5)(ALL) 岗位/人事部门(4) 采购(74) 岗位
            var depIds = new List<int>() { 7, 3, 5 };
            var jobIds = new List<int>() { 1, 74 };
            groupsTaskAssignments.AddRange(
                usersData
                .Where(it => depIds.Contains(it.DepId) || jobIds.Contains(it.JobPostId))
                .Select(it => new Grp_GroupsTaskAssignment()
                {
                    DIId = diId,
                    CTId = 98,
                    UId = it.Id,
                    CreateUserId = userId
                }).ToList()
            );

            //2024年8月26日16点27分 “其他款项分配给王鸽(UserID：149)”
            groupsTaskAssignments.Add(new Grp_GroupsTaskAssignment()
            {
                DIId = diId,
                CTId = 98,
                UId = 149,
                CreateUserId = userId
            }
            );

            //1015 超支费用,285 收款退还  --> 财务部门(ALL)岗位
            groupsTaskAssignments.AddRange(
                usersData
                .Where(it => it.DepId == 3)
                .Select(it => new Grp_GroupsTaskAssignment()
                {
                    DIId = diId,
                    CTId = 1015,
                    UId = it.Id,
                    CreateUserId = userId
                }).ToList()
            );

            groupsTaskAssignments.AddRange(
                usersData
               .Where(it => it.DepId == 3)
               .Select(it => new Grp_GroupsTaskAssignment()
               {
                   DIId = diId,
                   CTId = 285,
                   UId = it.Id,
                   CreateUserId = userId
               }).ToList()
           );

            //1015 超支费用  --> 国交部(ALL)岗位
            groupsTaskAssignments.AddRange(
                usersData
              .Where(it => it.DepId == 7)
              .Select(it => new Grp_GroupsTaskAssignment()
              {
                  DIId = diId,
                  CTId = 1015,
                  UId = it.Id,
                  CreateUserId = userId
              }).ToList()
            );


            if (groupsTaskAssignments.Count > 0)
            {
                var addId = await _teamRateRep._sqlSugar.Insertable(groupsTaskAssignments).ExecuteCommandAsync();
            }

            result.Code = 0;
            result.Msg = string.Format(@"操作成功!");

            return result;

        }

        #endregion

        #region 员工是否是市场部人员

        /// <summary>
        /// 验证市场部客户人员
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static async Task<bool> IsMarketingStaff(int userId)
        {
            var userInfos = await _dirRep._sqlSugar
                                          .Queryable<Sys_Users>()
                                          .InnerJoin<Sys_Department>((u, d) => u.DepId == d.Id)
                                          .Where((u, d) => u.IsDel == 0 && u.Id == userId && d.DepName.Contains("市场部"))
                                          .ToListAsync();
            if (userInfos.Count > 0) return true;

            return false;
        }

        #endregion

        #region 三公费用相关

        /// <summary>
        /// 保存操作前汇率
        /// </summary>
        /// <param name="currUserId"></param>
        /// <param name="diId"></param>
        /// <param name="linkModule"></param>
        /// <returns></returns>
        public static async Task<bool> RateRecordSave(int currUserId, int diId, string linkModule)
        {
            //var selectData = await _enterExitCostRep._sqlSugar.Queryable<Sys_ExchangeRateRecord>()
            //    .Where(x => Convert.ToDateTime(x.RateDateTime).ToString("yyyy-MM-dd").Equals(DateTime.Now.ToString("yyyy-MM-dd")))
            //    .FirstAsync();
            //if (selectData != null) return false;

            var _currencyRate = await _juHeApi.PostItemRateAsync(Array.Empty<string>());

            string rateInfoStr = string.Empty;

            var oaCurrencyData = await _sqlSugar.Queryable<Sys_SetData>()
                .Where(x => x.IsDel == 0 && x.STid == 66)
                .ToListAsync();

            foreach (var rate in _currencyRate)
            {
                var oaCurrncy = oaCurrencyData.Find(x => x.Remark.Equals(rate.Name));
                if (oaCurrncy == null) continue;

                //美元(USD):7.5|
                string rateStr = rate.FSellPri ?? "0.00";
                rateInfoStr += $"{rate.Name}({oaCurrncy.Name}):{rateStr}|";
            }

            if (string.IsNullOrEmpty(rateInfoStr)) return false;

            var add = await _sqlSugar
                .Insertable<Sys_ExchangeRateRecord>(new Sys_ExchangeRateRecord()
                {
                    DiId = diId,
                    LinkModule = linkModule,
                    RateDateTime = DateTime.Now,
                    RateInfo = rateInfoStr,
                    CreateUserId = currUserId,
                    IsDel = 0
                })
                .ExecuteCommandAsync();

            return add > 0;
        }

        /// <summary>
        /// 三公费用导入收款账单数据
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public static async Task<List<Fin_ForeignReceivables>> ReceivablesImportFeeAsync(int groupId)
        {
            var data = new List<Fin_ForeignReceivables>();

            var enterExitCosts = await _sqlSugar.Queryable<Grp_EnterExitCost>().Where(it => it.IsDel == 0 && it.DiId == groupId).FirstAsync();
            var dayAndCosts = await _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0 && it.DiId == groupId).ToListAsync();
            var dayOtherCosts = await _sqlSugar.Queryable<Grp_DayOtherPrice>().Where(x => x.IsDel == 0 && x.Diid == groupId).ToListAsync(); //其他款项
            if (enterExitCosts == null) return data;

            //数据源
            var stayData = dayAndCosts.Where(it => it.Type == 1).ToList();          //住宿费
            var mealData = dayAndCosts.Where(it => it.Type == 2).ToList();          //伙食费
            var miscellaneousData = dayAndCosts.Where(it => it.Type == 3).ToList(); //公杂费
            var trainData = dayAndCosts.Where(it => it.Type == 4).ToList();         //培训费

            //筛选 陪同人员 = 1
            var groupClientList = await _sqlSugar.Queryable<Grp_TourClientList>()
                                                 .LeftJoin<Crm_DeleClient>((tcl, dc) => tcl.ClientId == dc.Id)
                                                 .LeftJoin<Crm_CustomerCompany>((tcl, dc, cc) => dc.CrmCompanyId == cc.Id)
                                                 .LeftJoin<Sys_SetData>((tcl, dc, cc, sd) => tcl.ShippingSpaceTypeId == sd.Id)
                                                 .Where(tcl => tcl.IsDel == 0 &&
                                                               tcl.DiId == groupId &&
                                                               tcl.IsAccompany == 1
                                                        )
                                                 .Select((tcl, dc, cc, sd) => new
                                                 {
                                                     DiId = tcl.DiId,
                                                     CompanyId = cc.Id,
                                                     CompanyName = cc.CompanyFullName,
                                                     ClienId = dc.Id,
                                                     ClientName = dc.FirstName + dc.LastName,
                                                     SpaceId = tcl.ShippingSpaceTypeId,
                                                     SpaceName = sd.Name
                                                 })
                                                 .ToListAsync();
            if (!groupClientList.Any()) return data;

            decimal domesticFeeTotal = 0.00M,        //境内费用
                    economyClassFeeTotal = 0.00M,    //经济舱费用
                    businessClassFeeTotal = 0.00M,   //公务舱费用
                    firstClassFeeTotal = 0.00M,      //头等舱费用
                    stayFeeTotal = 0.00M,            //住宿费
                    mealsFeeTotal = 0.00M,           //餐食费
                    miscellaneousFeeTotal = 0.00M,   //公杂费
                    trainFeeTotal = 0.00M,           //培训费
                    otherPriceTotal = 0.00M;         //其他款项

            //境内费用(其他费用)
            if (enterExitCosts.ChoiceOne == 1) domesticFeeTotal = enterExitCosts.InsidePay;

            //住宿费
            if (enterExitCosts.ChoiceThree == 1) stayFeeTotal = stayData.Sum(x => x.SubTotal);

            //伙食费
            if (enterExitCosts.ChoiceFour == 1) mealsFeeTotal = mealData.Sum(x => x.SubTotal);

            //公杂费
            if (enterExitCosts.ChoiceFive == 1) miscellaneousFeeTotal = miscellaneousData.Sum(x => x.SubTotal);

            //培训费
            if (enterExitCosts.ChoiceSix == 1) trainFeeTotal = trainData.Sum(x => x.SubTotal);

            //其他款项
            if (enterExitCosts.OtherExpenses_Checked == 1) otherPriceTotal = dayOtherCosts.Sum(x => x.SubTotal);

            decimal otherFeeTotal = domesticFeeTotal + stayFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + trainFeeTotal + otherPriceTotal;

            //国际旅费合计
            //经济舱
            if (enterExitCosts.SumJJC == 1) economyClassFeeTotal = enterExitCosts.OutsideJJPay + otherFeeTotal;
            //公务舱
            if (enterExitCosts.SumGWC == 1) businessClassFeeTotal = enterExitCosts.OutsideGWPay + otherFeeTotal;
            //头等舱
            if (enterExitCosts.SumTDC == 1) firstClassFeeTotal = enterExitCosts.OutsideTDPay + otherFeeTotal;

            var groupClientListGroup = groupClientList.GroupBy(x => x.CompanyId);

            foreach (var item in groupClientListGroup)
            {
                var companyName = AesEncryptionHelper.Decrypt(item.FirstOrDefault().CompanyName);


                var airTicketGroup = item.GroupBy(x => x.SpaceId);

                foreach (var airTicket in airTicketGroup)
                {
                    int quantity = airTicket.Count();
                    if (quantity > 0)
                    {
                        var price = 0.00M;
                        var spaceName = airTicket.FirstOrDefault()?.SpaceName ?? string.Empty;
                        if (spaceName.Equals("经济舱")) price = economyClassFeeTotal;
                        else if (spaceName.Equals("公务舱")) price = businessClassFeeTotal;
                        else if (spaceName.Equals("头等舱")) price = firstClassFeeTotal;

                        if (price > 0)
                        {
                            decimal itemTotal = price * quantity;

                            data.Add(new Fin_ForeignReceivables()
                            {
                                Id = 0,
                                CreateTime = DateTime.Now,
                                Diid = groupId,
                                PriceName = $"{companyName}-{spaceName}",
                                Price = price,
                                Count = quantity,
                                Unit = "人",
                                ItemSumPrice = itemTotal,
                                Currency = 836,
                                Rate = 1.0000M,
                                AddingWay = 2,
                                Remark = "由出入境费用导入费用",
                            });
                        }
                    }
                }
            }

            return data;
        }

        #endregion


        #region 根据团ID获取各板块实际操作人

        /// <summary>
        /// 获取 团组模块操作人
        /// </summary>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public static List<GroupOperationUserView> GetGroupModuleOperators(int groupId)
        {
            var modoule = new GroupOperationUserView();
            var view = modoule.GetOperationUsersInit();
            if (groupId < 1) return view;
            var groupInfo = _sqlSugar.Queryable<Grp_DelegationInfo>()
                .Where(it => it.IsDel == 0 && it.Id == groupId)
                .First();
            if (groupInfo == null) return view;

            //获取团组下的操作人
            //车/导游地接
            var opUers = _sqlSugar.Queryable<Grp_CarTouristGuideGroundReservations>()
                .InnerJoin<Grp_CreditCardPayment>((x, y) => x.Id == y.CId && y.CTable == 79)
                .LeftJoin<Sys_Users>((x, y, u) => x.CreateUserId == u.Id)
                .Where((x, y, u) => x.DiId == groupId && x.IsDel == 0)
                .Select((x, y, u) => new GroupOperationUserInfo
                {
                    UserId = u.Id,
                    UserName = u.CnName
                }).Distinct().ToList();
            view.Where(it => it.CTableId == 79).FirstOrDefault().OperationUsers = opUers;

            //签证 
            var visaUsers = _sqlSugar.Queryable<Grp_VisaInfo>()
                .InnerJoin<Grp_CreditCardPayment>((x, y) => x.Id == y.CId && y.CTable == 80)
                .LeftJoin<Sys_Users>((x, y, u) => x.CreateUserId == u.Id)
                .Where((x, y, u) => x.DIId == groupId && x.IsDel == 0)
                .Select((x, y, u) => new GroupOperationUserInfo
                {
                    UserId = u.Id,
                    UserName = u.CnName
                }).Distinct().ToList();
            view.Where(it => it.CTableId == 80).FirstOrDefault().OperationUsers = visaUsers;

            //保险 
            var insUsers = _sqlSugar.Queryable<Grp_Customers>()
                .InnerJoin<Grp_CreditCardPayment>((x, y) => x.Id == y.CId && y.CTable == 82)
                .LeftJoin<Sys_Users>((x, y, u) => x.CreateUserId == u.Id)
                .Where((x, y, u) => x.DiId == groupId && x.IsDel == 0)
                .Select((x, y, u) => new GroupOperationUserInfo
                {
                    UserId = u.Id,
                    UserName = u.CnName
                }).Distinct().ToList();
            view.Where(it => it.CTableId == 82).FirstOrDefault().OperationUsers = insUsers;

            //邀请/公务活动 
            var ioaUsers = _sqlSugar.Queryable<Grp_InvitationOfficialActivities>()
                .InnerJoin<Grp_CreditCardPayment>((x, y) => x.Id == y.CId && y.CTable == 81)
                .LeftJoin<Sys_Users>((x, y, u) => x.CreateUserId == u.Id)
                .Where((x, y, u) => x.DiId == groupId && x.IsDel == 0)
                .Select((x, y, u) => new GroupOperationUserInfo
                {
                    UserId = u.Id,
                    UserName = u.CnName
                }).Distinct().ToList();
            if (!ioaUsers.Any())
            {
                ioaUsers = _sqlSugar.Queryable<Res_OfficialActivities>()
                    .LeftJoin<Sys_Users>((x, y) => x.CreateUserId == y.Id)
                    .Where((x, y) => x.DiId == groupId && x.IsDel == 0)
                    .Select((x, y) => new GroupOperationUserInfo
                    {
                        UserId = y.Id,
                        UserName = y.CnName
                    }).Distinct().ToList();
            }
            view.Where(it => it.CTableId == 81).FirstOrDefault().OperationUsers = ioaUsers;

            //其他款项 98
            var otherUsers = _sqlSugar.Queryable<Grp_DecreasePayments>()
                .InnerJoin<Grp_CreditCardPayment>((x, y) => x.Id == y.CId && y.CTable == 98)
                .LeftJoin<Sys_Users>((x, y, u) => x.CreateUserId == u.Id)
                .Where((x, y, u) => x.DiId == groupId && x.IsDel == 0)
                .Select((x, y, u) => new GroupOperationUserInfo
                {
                    UserId = u.Id,
                    UserName = u.CnName
                }).Distinct().ToList();
            view.Where(it => it.CTableId == 98).FirstOrDefault().OperationUsers = otherUsers;

            //添加分配人员
            // 79	车/导游地接
            // 80	签证
            // 81	邀请/公务活动
            var setdataids = new List<int>() { 79, 81 };

            // 27	商邀
            // 28	OP
            var jobSetdataids = new List<int>() { 27, 28 };

            var q = _sqlSugar.Queryable<Grp_GroupsTaskAssignment, Sys_Users>(
            (ccta, su) => new JoinQueryInfos(
                JoinType.Left, ccta.UId == su.Id && su.IsDel == 0))
            .Where((ccta, su) =>
                    ccta.IsDel == 0 &&
                    ccta.DIId == groupId &&
                    jobSetdataids.Contains(su.JobPostId) &&
                    setdataids.Contains(ccta.CTId))
            .Select((ccta, su) => new
            {
                userid = su.Id,
                su.JobPostId,
                ccta.CTId,
                ccta.DIId,
                su.CnName,
            });

            var qtb = q.ToDataTable();

            foreach (var groupModule in view)
            {
                if (setdataids.Contains(groupModule.CTableId) && !groupModule.OperationUsers.Any())
                {
                    var qtbUsers = qtb.AsEnumerable().
                        Where(x => x.Field<int>("CTId") == groupModule.CTableId).
                        Select(x => x.Field<int>("userid")).ToList();
                    groupModule.OperationUsers = qtbUsers.Select(x => new GeneralMethod.GroupOperationUserInfo()
                    {
                        UserId = x,
                        UserName = qtb.AsEnumerable().FirstOrDefault(y => y.Field<int>("userid") == x)?.Field<string>("CnName") ?? ""
                    }).ToList();
                }
            }

            return view;
        }

        public class GroupOperationUserView
        {
            public int CTableId { get; set; }
            public string CTableName { get; set; }
            public List<GroupOperationUserInfo> OperationUsers { get; set; }

            public GroupOperationUserView() { }

            /// <summary>
            /// 默认模块List
            /// </summary>
            /// <returns></returns>
            public List<GroupOperationUserView> GetOperationUsersInit()
            {
                var modules = new List<GroupOperationUserView>() {
                    new(){ CTableId = 76,CTableName = "酒店预订",
                        OperationUsers=new List<GroupOperationUserInfo>(){ new() {UserId = 226,UserName = "宋夏雨" } } },
                    new(){ CTableId = 79,CTableName = "车/导游地接" },
                    new(){ CTableId = 80,CTableName = "签证" },
                    new(){ CTableId = 82,CTableName = "团组客户保险" },
                    new(){ CTableId = 81,CTableName = "邀请/公务活动" },
                    new(){ CTableId = 85,CTableName = "机票预订",
                        OperationUsers=new List<GroupOperationUserInfo>(){ new() {UserId = 385, UserName = "雍婷玉" } }  },
                    new(){ CTableId = 98,CTableName = "其他款项" },
                };

                return modules;
            }
        }
        public class GroupOperationUserInfo
        {
            public int UserId { get; set; }
            public string UserName { get; set; }
        }
        #endregion

        #endregion

        #region 团组汇率

        /// <summary>
        /// 团组汇率 
        /// 获取板块 币种 及 汇率
        /// 76	酒店预订  77	行程          79	车/导游地接
        /// 80	签证      82	团组客户保险  85	机票预订
        /// 98	其他款项  285	收款退还
        /// </summary>
        /// <param name="teamRateModels"></param>
        /// <param name="cTable"></param>
        /// <param name="currencyCodes"></param>
        /// <returns>
        /// string 
        /// eg: CNY 1.0000 
        /// </returns>
        public static async Task<string> PostGroupRateByCTableAndCurrency(List<TeamRateModelView> teamRateModels, int cTable, List<string> currencyCodes)
        {
            string str = "";

            List<string> currencyRates = new List<string>();
            TeamRateModelView hotelRateData = teamRateModels.Where(it => it.CTableId == cTable).FirstOrDefault();
            if (hotelRateData != null)
            {
                var hotelRates = hotelRateData.TeamRates;

                foreach (var item in currencyCodes)
                {
                    if (!string.IsNullOrEmpty(item))
                    {
                        var hotelRateInfo = hotelRates.Where(it => it.CurrencyCode.Equals(item)).FirstOrDefault();
                        if (hotelRateInfo != null)
                        {
                            string str1 = string.Format("{0} {1}\r\n", hotelRateInfo.CurrencyCode, hotelRateInfo.Rate.ToString("#0.0000"));
                            currencyRates.Add(str1);
                        }
                    }
                }

                if (currencyRates != null || currencyRates.Count > 0)
                {
                    currencyRates = currencyRates.Distinct().ToList();
                    foreach (var item in currencyRates)
                    {
                        str += item;
                    }

                }
            }
            return str;
        }

        /// <summary>
        /// 团组汇率 币种 Item (来源：团组汇率)
        /// 根据 团组Id And 业务类型(CTable)Id
        /// api处理CTable = 285,默认返回CNY
        /// </summary>
        /// <param name="portType"></param>
        /// <param name="diId"></param>
        /// <param name="cTable"></param>
        /// <returns></returns>
        public static async Task<TeamRateModelGeneralView> PostGroupTeamRateByDiIdAndCTableId(int portType, int diId, int cTable)
        {
            TeamRateModelGeneralView _view = new TeamRateModelGeneralView();
            _view = await _teamRateRep.PostGroupTeamRateByDiIdAndCTableId(portType, diId, cTable);
            return _view;
        }

        /// <summary>
        /// 汇率备注拆分
        /// (美元(USD):7.2370|日元(JPY):0.0499|欧元(EUR):8.3000|英镑(GBP):9.1996|港币(HKD):0.9291)
        /// </summary>
        /// <param name="rateRemark"></param>
        /// <returns></returns>
        public static async Task<List<TeamRateDescAddCurrencyIdView>> SplitExchangeRate(this string rateRemark)
        {
            List<TeamRateDescAddCurrencyIdView> _view = new List<TeamRateDescAddCurrencyIdView>();

            List<SetDataInfoView> currencyDatas = new List<SetDataInfoView>();
            #region 获取所有币种

            string sql = string.Format(@"select * from Sys_SetData where STid = {0} and isdel = 0", 66);
            var DBdata = _setDataRep.GetListBySqlWithNolock(sql);

            if (DBdata == null || DBdata.Count == 0)
            {
                return _view;
            }

            currencyDatas = DBdata.Select(x => new SetDataInfoView
            {
                Name = x.Name,
                Id = x.Id,
                Remark = x.Remark,
            }).ToList();

            #endregion


            #region 拆分remark里的汇率

            if (string.IsNullOrEmpty(rateRemark))
            {
                return _view;
            }
            if (rateRemark.Contains("|"))
            {
                string[] currencyArr = rateRemark.Split("|");
                foreach (string currency in currencyArr)
                {
                    string[] currency1 = currency.Split(":");
                    string[] currency2 = currency1[0].Split("(");

                    string currencyCode = currency2[1].Replace(")", "").TrimEnd();
                    TeamRateDescAddCurrencyIdView rateDescView = new TeamRateDescAddCurrencyIdView()
                    {
                        CurrencyId = currencyDatas.Find(it => it.Name == currencyCode).Id,
                        CurrencyCode = currencyCode,
                        CurrencyName = currency2[0],
                        Rate = decimal.Parse(currency1[1]),
                    };
                    _view.Add(rateDescView);
                }
            }
            else
            {
                try
                {
                    string[] currency1 = rateRemark.Split(":");
                    string[] currency2 = currency1[0].Split("(");
                    string currencyCode = currency2[1].Replace(")", "").TrimEnd();
                    TeamRateDescAddCurrencyIdView rateDescView = new TeamRateDescAddCurrencyIdView()
                    {
                        CurrencyId = currencyDatas.Find(it => it.Name == currencyCode).Id,
                        CurrencyCode = currencyCode,
                        CurrencyName = currency2[0],
                        Rate = decimal.Parse(currency1[1]),
                    };
                    _view.Add(rateDescView);
                }
                catch (Exception)
                {

                    return _view;
                }
            }
            #endregion

            return _view;
        }

        #endregion

        #region 汉字转换拼音
        private static Dictionary<int, List<string>> GetTotalPingYinDictionary(string text)
        {
            var chs = text.ToCharArray();

            //记录每个汉字的全拼
            Dictionary<int, List<string>> totalPingYinList = new Dictionary<int, List<string>>();

            for (int i = 0; i < chs.Length; i++)
            {
                var pinyinList = new List<string>();

                //是否是有效的汉字
                if (ChineseChar.IsValidChar(chs[i]))
                {
                    ChineseChar cc = new ChineseChar(chs[i]);
                    pinyinList = cc.Pinyins.Where(p => !string.IsNullOrWhiteSpace(p)).ToList();
                }
                else
                {
                    pinyinList.Add(chs[i].ToString());
                }

                //去除声调，转小写
                pinyinList = pinyinList.ConvertAll(p => Regex.Replace(p, @"\d", "").ToLower());

                //去重
                pinyinList = pinyinList.Where(p => !string.IsNullOrWhiteSpace(p)).Distinct().ToList();
                if (pinyinList.Any())
                {
                    totalPingYinList[i] = pinyinList;
                }
            }

            return totalPingYinList;
        }
        /// <summary>
        /// 获取汉语拼音全拼
        /// </summary>
        /// <param name="text">The string.</param>
        /// <returns></returns>
        public static List<string> GetTotalPingYin(this string text)
        {
            var result = new List<string>();
            foreach (var pys in GetTotalPingYinDictionary(text))
            {
                var items = pys.Value;
                if (result.Count <= 0)
                {
                    result = items;
                }
                else
                {
                    //全拼循环匹配
                    var newTotalPingYinList = new List<string>();
                    foreach (var totalPingYin in result)
                    {
                        newTotalPingYinList.AddRange(items.Select(item => totalPingYin + item));
                    }
                    newTotalPingYinList = newTotalPingYinList.Distinct().ToList();
                    result = newTotalPingYinList;
                }
            }
            return result;
        }

        /// <summary>
        /// 获取中文第一个拼音
        /// </summary>
        /// <param name="text"></param>
        /// <param name="isUp"></param>
        /// <returns></returns>
        public static string GetTotalPingYinFirst(this string text, bool isUp = true)
        {
            var returnstr = string.Empty;
            var enResult = text.GetTotalPingYin();
            if (enResult.Count == 0)
            {
                returnstr = text;
            }
            else if (isUp)
            {
                returnstr = enResult[0].ToUpper();
            }
            else
            {
                returnstr = enResult[0].ToLower();
            }
            return returnstr;
        }

        /// <summary>
        /// 获取汉语拼音首字母
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        public static List<string> GetFirstPingYin(this string text)
        {
            var result = new List<string>();
            foreach (var pys in GetTotalPingYinDictionary(text))
            {
                var items = pys.Value;
                if (result.Count <= 0)
                {
                    result = items.ConvertAll(p => p.Substring(0, 1)).Distinct().ToList();
                }
                else
                {
                    //首字母循环匹配
                    var newFirstPingYinList = new List<string>();
                    foreach (var firstPingYin in result)
                    {
                        newFirstPingYinList.AddRange(items.Select(item => firstPingYin + item.Substring(0, 1)));
                    }
                    newFirstPingYinList = newFirstPingYinList.Distinct().ToList();
                    result = newFirstPingYinList;
                }
            }
            return result;
        }
        #endregion

        #region 新客户资料表 操作记录

        /// <summary>
        /// 新客户资料表
        /// 操作记录添加
        /// </summary>
        /// <param name="portType"></param>
        /// <param name="operationEnum"></param>
        /// <param name="userId"></param>
        /// <param name="dataId"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public static async Task<bool> NewClientOperationRecord(int portType, OperationEnum operationEnum, int userId, int dataId, string remark)
        {
            Crm_TableOperationRecord _TableOperationRecord = new Crm_TableOperationRecord()
            {
                TableName = "Crm_NewClientData",
                PortType = portType,
                OperationItem = operationEnum,
                DataId = dataId,
                CreateUserId = userId,
                CreateTime = DateTime.Now,
                Remark = remark
            };

            bool add = await _tableOperationRecordRep._Add(_TableOperationRecord);
            if (add) return false;
            return false;
        }

        #endregion

        #region 金额转大写
        /// <summary>
        /// 金额转换为大写数字
        /// <para>1、支持的最大数字：999999999999.99（玖仟玖佰玖拾玖亿玖仟玖佰玖拾玖万玖仟玖佰玖拾玖元玖角玖分）</para>
        /// <para>2、小数点后最多支持两位</para>
        /// </summary>
        /// <param name="num">数值</param>
        /// <returns></returns>
        public static string ConvertCNYUpper(this decimal num)
        {
            if (num == 0)
            {
                return "零元";
            }
            // 解决327000000转换后缺少单位万的问题
            return ConvertToChinese(num);
        }

        #region 实例
        /// <summary>
        /// 要转换的数字
        /// </summary>
        private static decimal j;

        /// <summary>
        /// 
        /// </summary>
        private static string[] NumChineseCharacter = new string[] { "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖" };

        /// <summary>
        /// 判断输入的数字是否大于double类型
        /// </summary>
        private static bool IsNumber
        {
            get
            {
                if (j > decimal.MaxValue || j <= 0)
                    return false;
                else
                    return true;
            }
        }

        /// <summary>
        /// 数字转换成大写汉字主函数
        /// </summary>
        /// <returns>返回转换后的大写汉字</returns>
        public static string ConvertToChinese(decimal m)
        {
            j = m;
            string bb = "";
            if (IsNumber)
            {
                string str = j.ToString();
                string[] Num = str.Split('.');
                if (Num.Length == 1)
                {
                    bb = NumberString(Num[0]) + "元整";
                    bb = bb.Replace("零零", "零");
                }
                else
                {
                    bb = NumberString(Num[0]) + "元";
                    bb += FloatString(Num[1]);
                    bb = bb.Replace("零零", "零");
                }
            }
            else
            {
                throw new FormatException("你输入的数字格式不正确或不是数字!");
            }
            return bb;
        }
        /// <summary>
        /// 小数位转换只支持两位的小数
        /// </summary>
        /// <param name="Num">转换的小数</param>
        /// <returns>小数转换成汉字</returns>
        private static string FloatString(string Num)
        {
            string cc = "";
            if (Num.Length > 2)
            {
                throw new FormatException("小数位数过多.");
            }
            else
            {
                string bb = ConvertString(Num);
                int len = bb.IndexOf("零");
                if (len != 0)
                {
                    bb = bb.Replace("零", "");
                    if (bb.Length == 1)
                    {
                        cc = bb.Substring(0, 1) + "角";
                    }
                    else
                    {
                        cc = bb.Substring(0, 1) + "角";
                        cc += bb.Substring(1, 1) + "分";
                    }
                }
                else
                    cc = bb + "分";
            }
            return cc;
        }
        /// <summary>
        /// 判断数字位数以进行拆分转换
        /// </summary>
        /// <param name="Num">要进行拆分的数字</param>
        /// <returns>转换成的汉字</returns>
        private static string NumberString(string Num)
        {
            string bb = "";
            if (Num.Length <= 4)
            {
                bb = Convert4(Num);
            }
            else if (Num.Length > 4 && Num.Length <= 8)
            {
                bb = Convert4(Num.Substring(0, Num.Length - 4)) + "万";
                bb += Convert4(Num.Substring(Num.Length - 4, 4));
            }
            else if (Num.Length > 8 && Num.Length <= 12)
            {
                bb = Convert4(Num.Substring(0, Num.Length - 8)) + "亿";
                if (Convert4(Num.Substring(Num.Length - 8, 4)) == "")
                    if (Convert4(Num.Substring(Num.Length - 4, 4)) != "")
                        bb += "零";
                    else
                        bb += "";
                else
                    bb += Convert4(Num.Substring(Num.Length - 8, 4)) + "万";
                bb += Convert4(Num.Substring(Num.Length - 4, 4));
            }
            else
            {
                throw new Exception("整数部分最多支持12位");
            }
            return bb;
        }
        /// <summary>
        /// 四位数字的转换
        /// </summary>
        /// <param name="Num">准备转换的四位数字</param>
        /// <returns>转换以后的汉字</returns>
        private static string Convert4(string Num)
        {
            string bb = "";
            if (Num.Length == 1)
            {
                bb = ConvertString(Num);
            }
            else if (Num.Length == 2)
            {
                bb = ConvertString(Num);
                bb = Convert2(bb);
            }
            else if (Num.Length == 3)
            {
                bb = ConvertString(Num);
                bb = Convert3(bb);
            }
            else
            {
                bb = ConvertString(Num);
                string cc = "";
                string len = bb.Substring(0, 4);
                if (len != "零零零零")
                {
                    len = bb.Substring(0, 3);
                    if (len != "零零零")
                    {
                        bb = bb.Replace("零零零", "");
                        if (bb.Length == 1)
                        {
                            bb = bb.Substring(0, 1) + "仟";
                        }
                        else
                        {
                            if (bb.Substring(0, 1) != "零" && bb.Substring(0, 2) != "零")
                                cc = bb.Substring(0, 1) + "仟";
                            else
                                cc = bb.Substring(0, 1);
                            bb = cc + Convert3(bb.Substring(1, 3));
                        }
                    }
                    else
                    {
                        bb = bb.Replace("零零零", "零");
                    }
                }
                else
                {
                    bb = bb.Replace("零零零零", "");
                }
            }
            return bb;
        }
        /// <summary>
        /// 将数字转换成汉字
        /// </summary>
        /// <param name="Num">需要转换的数字</param>
        /// <returns>转换后的汉字</returns>
        private static string ConvertString(string Num)
        {
            string bb = "";
            for (int i = 0; i < Num.Length; i++)
            {
                bb += NumChineseCharacter[int.Parse(Num.Substring(i, 1))];
            }
            return bb;
        }
        /// <summary>
        /// 两位数字的转换
        /// </summary>
        /// <param name="Num">两位数字</param>
        /// <returns>转换后的汉字</returns>
        private static string Convert2(string Num)
        {
            string bb = ""; string cc = "";
            string len = Num.Substring(0, 1);
            if (len != "零")
            {
                bb = Num.Replace("零", "");
                if (bb.Length == 1)
                {
                    cc = bb.Substring(0, 1) + "拾";
                }
                else
                {
                    cc = bb.Substring(0, 1) + "拾";
                    cc += bb.Substring(1, 1);
                }
            }
            else
                cc = Num;
            return cc;
        }
        /// <summary>
        /// 三位数字的转换
        /// </summary>
        /// <param name="Num">三位数字</param>
        /// <returns>转换后的汉字</returns>
        private static string Convert3(string Num)
        {
            string bb = ""; string cc = "";
            string len = Num.Substring(0, 2);
            if (len != "零零")
            {
                bb = Num.Replace("零零", "");
                if (bb.Length == 1)
                {
                    bb = bb.Substring(0, 1) + "佰";
                }
                else
                {
                    if (bb.Substring(0, 1) != "零")
                        cc = bb.Substring(0, 1) + "佰";
                    else
                        cc = bb.Substring(0, 1);
                    bb = cc + Convert2(bb.Substring(1, 2));
                }
            }
            else
            {
                bb = Num.Replace("零零", "零");
            }
            return bb;
        }
        #endregion

        #endregion

        #region 数字验证
        /// <summary>
        /// 验证数字字符串
        /// </summary>
        /// <param name="numStr"></param>
        /// <returns></returns>
        public static bool IsNumeric(this string numStr)
        {
            bool isNumeric = Regex.IsMatch(numStr, @"^\d+$");

            if (isNumeric)
            {
                return true;
            }
            return false;
        }


        #endregion

        #region 日期 格式转换 

        /// <summary>
        /// 验证日期字符串
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string ConvertToDatetime(this string str)
        {
            if (!string.IsNullOrEmpty(str))
            {
                DateTime currentDate = Convert.ToDateTime(str);

                return $"{currentDate.Year}年{currentDate.Month}月{currentDate.Day}日";
            }

            return "";
        }


        #endregion

        #region 月份/季度

        /// <summary>
        /// 获取月份对应的起始天数
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public static List<MonthInfo> GetMonthInfos(int year)
        {
            List<MonthInfo> months = new List<MonthInfo>();

            if (year > 1)
            {
                for (int i = 1; i < 13; i++)
                {
                    months.Add(new MonthInfo()
                    {
                        Month = i,
                        Days = new DaysInfo() { BeginDays = 1, EndDays = DateTime.DaysInMonth(year, i) }
                    });
                }
            }
            return months;
        }


        /// <summary>
        /// 获取季度对应的起始天数
        /// </summary>
        /// <param name="year"></param>
        /// <returns></returns>
        public static List<MonthInfo> GetQuarter(int year)
        {
            List<MonthInfo> months = new List<MonthInfo>();

            months.Add(new MonthInfo() { Month = 1, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } });  //1.1 - 3.31
            months.Add(new MonthInfo() { Month = 2, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //4.1 - 6.30
            months.Add(new MonthInfo() { Month = 3, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //7.1 - 9.31
            months.Add(new MonthInfo() { Month = 4, Days = new DaysInfo() { BeginDays = 1, EndDays = 31 } }); //10.1 - 12.31

            return months;
        }
        public class MonthInfo
        {
            public int Month { get; set; }

            public DaysInfo Days { get; set; }
        }

        public class DaysInfo
        {
            public int BeginDays { get; set; }
            public int EndDays { get; set; }
        }

        #endregion

        #region op行程单，黑屏幕代码

        public static string GetCountryStandingTime(int diId)
        {
            DataTable datas = GetTableByBlackCode(diId);

            string countryStr = "[黑屏代码未录入].";
            if (datas.Rows.Count > 0)
            {
                var airDatas = from row in datas.AsEnumerable()
                               select new
                               {
                                   Three = row.Field<string>("Three"),
                                   Day = row.Field<string>("Day"),
                                   ArrivedDate = row.Field<string>("ArrivedDate"),
                               };


                //三字码信息
                List<Res_ThreeCode> listcode = _dirRep._sqlSugar.Queryable<Res_ThreeCode>().Where(x => x.IsDel == 0).ToList();
                //string countryStr = "";
                int index = 0;
                List<string> cityCodes = new List<string>();
                //去掉开始和结束城市
                foreach (var row in airDatas)
                {
                    if (!string.IsNullOrEmpty(row.Three))
                    {
                        if (index == 0) cityCodes.Add(row.Three.Substring(3, 3)); //到达国家
                        else if (airDatas.Count() - 1 == index) cityCodes.Add(row.Three.Substring(0, 3)); //到达国家
                        else cityCodes.Add(row.Three.Substring(0, 3)); //到达国家
                    }
                    index++;
                }
                cityCodes = cityCodes.Distinct().ToList();

                if (cityCodes.Count > 0) countryStr = "";

                foreach (var item in cityCodes)
                {
                    var airData = airDatas.Where(it => it.Three.Contains(item)).ToList();
                    string country = listcode.Find(it => it.Three.Equals(item))?.Country ?? "Unknown";
                    if (country.Equals("中国"))
                    {
                        country = listcode.Find(it => it.Three.Equals(item))?.City ?? "Unknown";
                    }
                    //int days = 0;
                    if (airData.Count == 2)
                    {
                        DateTime arr_dt = Convert.ToDateTime(airData[0].ArrivedDate); //抵达时间
                        DateTime dep_dt = Convert.ToDateTime(airData[1].Day); //离开时间
                        //days = (dep_dt - arr_dt).Days;
                        //countryStr += $@"{country}停留{days}日、";
                        countryStr += $@"{country}停留  日、";
                    }
                }
                if (countryStr.Length > 0) countryStr = countryStr.Substring(0, countryStr.Length - 1);

            }

            return countryStr;
        }

        /// <summary>
        ///根据机票黑屏代码整理DataTable
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public static DataTable GetTableByBlackCode1(int diid)
        {
            //黑屏代码信息
            List<Air_TicketBlackCode> listcode = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.DiId == diid && x.IsDel == 0).ToList();

            //测试数据为序号,航班号,起飞日期,三字码,起飞时刻,到达时刻,出发航站楼,到达航站楼,机型,飞行时间
            //1.3U8391   TU17NOV CTUCAI  0220 0715   T1 T2  330  10H55M
            DataTable dt = new DataTable();
            dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
            dt.Columns.Add("Date", typeof(string));//起飞日期
            dt.Columns.Add("Three", typeof(string));//三字码
            dt.Columns.Add("StartTime", typeof(string));//起飞时刻
            dt.Columns.Add("EndTime", typeof(string));//到达时刻
            dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
            dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
            dt.Columns.Add("AirModel", typeof(string)); //机型
            dt.Columns.Add("FlightTime", typeof(string));//飞行时间
            dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据 
            dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
            dt.Columns.Add("Error", typeof(string));//整理的到达日期
            dt.Columns.Add("Sign", typeof(string));//标识：0表示为原生黑屏代码、1表示“+1”新增的黑屏代码

            //判断是否录入黑屏代码
            if (listcode.Count == 0 || listcode == null)
            {
                dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "黑屏代码未录入!", null);
            }
            else
            {
                //读取单段黑屏代码
                for (int i = 0; i < listcode.Count; i++)
                {
                    //去除序号
                    string[] CodeList = Regex.Split(listcode[i].BlackCode, "\\d+\\.", RegexOptions.IgnoreCase);
                    //去除多余空格，方法一Linq扩展方法
                    CodeList = CodeList.Where(str => str != "").ToArray();
                    CodeList = CodeList.Where(str => str != " ").ToArray();

                    //年
                    int year = Convert.ToInt32(DateTime.Now.Year.ToString());

                    //读取单条黑屏代码
                    for (int j = 0; j < CodeList.Length; j++)
                    {
                        //去除多余空格，方法二使用Split()方法进行分割,分割有一个选项是RemoveEmptyEntries
                        CodeList[j] = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty).TrimStart().TrimEnd();
                        string[] Info = CodeList[j].Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                        //string[] Info = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty)
                        //                   .TrimStart().TrimEnd().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                        //去除多余空格
                        Info = Info.Where(str => str != "").ToArray();
                        Info = Info.Where(str => str != " ").ToArray();

                        //判断黑屏代码是否正确拆分; 理应拆成9段
                        if (Info.Length < 9)
                        {
                            dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "本团组第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误,请联系机票同事核对", null);
                        }
                        else
                        {
                            try
                            {
                                //月                  
                                int month = Convert.ToInt32(GetLonger(Info[1].Substring(4, 3)));
                                //日
                                int day = Convert.ToInt32(Info[1].Substring(2, 2));

                                #region 逐一比较月份,判断是否翻年;逐一比较三字码顶真,判断是否跑错机场
                                if (j > 0)
                                {
                                    string[] Temp = CodeList[j - 1].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty)
                                                  .TrimStart().TrimEnd().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
                                    Temp = Temp.Where(str => str != "").ToArray();
                                    Temp = Temp.Where(str => str != " ").ToArray();
                                    int monthTemp = Convert.ToInt32(GetLonger(Temp[1].Substring(4, 3)));
                                    // 如果相邻月份之差小于0,则证明次一条年份需+1
                                    if (month - monthTemp < 0)
                                    {
                                        year = year + 1;
                                    }
                                    //如果相邻代码三字码不顶真，提醒
                                    string FootThree = Temp[2].Substring(3, 3);
                                    string HeadThree = Info[2].Substring(0, 3);
                                    if (FootThree != HeadThree)
                                    {
                                        //DelegationInfoService s = new DelegationInfoService();
                                        //UsersService us = new UsersService();
                                        //GroupsTaskAssignmentService gts = new GroupsTaskAssignmentService();

                                        //77	行程
                                        List<Grp_GroupsTaskAssignment> list2 = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(x => x.CTId == 77 && x.DIId == diid && x.IsDel == 0).ToList();
                                        foreach (var temp in list2)
                                        {
                                            //if (temp.UId != 21)
                                            //SendAndReturn(us.GetUsersByID(temp.UId).Email,
                                            //    "黑屏代码提醒",
                                            //   s.GetDelegationInfoByID(diid).TeamName + "的机票黑屏代码中，相邻航段的三字码不连续，请查看！");
                                        }

                                        //85	机票预订
                                        List<Grp_GroupsTaskAssignment> list6 = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(x => x.CTId == 85 && x.DIId == diid && x.IsDel == 0).ToList();
                                        foreach (var temp in list6)
                                        {
                                            //if (temp.UId != 21)
                                            //SendAndReturn(us.GetUsersByID(temp.UId).Email,
                                            //    "黑屏代码提醒",
                                            //     s.GetDelegationInfoByID(diid).TeamName + "的机票黑屏代码中，相邻航段的三字码不连续，请查看！");
                                        }
                                    }

                                }
                                #endregion


                                #region 判断到达日期是否需要加1
                                if (Info[4].Contains("+"))
                                {
                                    //日期+1
                                    day = day + 1;

                                    //判断是否进入下一月
                                    if (day > Convert.ToInt32(GetDaysByMonth(Info[1].Substring(4, 3), year)))
                                    {
                                        day = day - Convert.ToInt32(GetDaysByMonth(Info[1].Substring(4, 3), year));
                                        month = month + 1;

                                        //判断是否进入下一年
                                        if (month > 12)
                                        {
                                            month = month - 12;
                                            year = year + 1;
                                        }
                                    }

                                    //月份整理格式
                                    string monthTemp = month.ToString();
                                    if (month < 10)
                                    {
                                        monthTemp = "0" + monthTemp;
                                    }
                                    //日期整理格式
                                    string daytemp = day.ToString();
                                    if (day < 10)
                                    {

                                        daytemp = "0" + daytemp;
                                    }

                                    string temp = Info[4].Split('+')[0];

                                    //添加起飞数据
                                    dt.Rows.Add(Info[0],
                                       Info[1],
                                       Info[2],
                                       Info[3],
                                       temp,
                                       Info[5],
                                       Info[6],
                                       Info[7],
                                       Info[8],
                                       year + "-" + GetLonger(Info[1].Substring(4, 3)) + "-" + Info[1].Substring(2, 2),
                                       year + "-" + monthTemp + "-" + daytemp,
                                       "",
                                       "0");
                                    //加1天，添加到达数据
                                    dt.Rows.Add(Info[0],
                                       Info[1].Replace(Info[1].Substring(2, 2), daytemp),
                                       Info[2],
                                       Info[3],
                                       temp,
                                       Info[5],
                                       Info[6],
                                       Info[7],
                                       Info[8],
                                       year + "-" + monthTemp + "-" + daytemp,
                                       year + "-" + monthTemp + "-" + daytemp,
                                       "",
                                       "1");
                                }
                                else
                                {
                                    //月份整理格式
                                    string monthTemp = month.ToString();
                                    if (month < 10)
                                    {
                                        monthTemp = "0" + monthTemp;
                                    }
                                    //日期整理格式
                                    string daytemp = day.ToString();
                                    if (day < 10)
                                    {

                                        daytemp = "0" + daytemp;
                                    }

                                    dt.Rows.Add(Info[0],
                                        Info[1],
                                        Info[2],
                                        Info[3],
                                        Info[4],
                                        Info[5],
                                        Info[6],
                                        Info[7],
                                        Info[8],
                                        year + "-" + monthTemp + "-" + daytemp,
                                        year + "-" + monthTemp + "-" + daytemp,
                                        "",
                                        "0");
                                }
                                #endregion
                            }
                            catch (Exception ex)
                            {
                                string exstr = ex.Message.ToString();
                            }
                        }

                    }

                    //排序
                    dt.DefaultView.Sort = "Day asc";
                    dt = dt.DefaultView.ToTable();
                }
            }

            return dt;
        }

        private readonly static string[] excludeArr = new string[] { "[中转]", "[转机]", "[国内]" };

        /// <summary>
        ///根据机票黑屏代码整理DataTable
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public static DataTable GetTableByBlackCode(int diid)
        {
            //黑屏代码信息
            List<Air_TicketBlackCode> listcode = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.DiId == diid && x.IsDel == 0).ToList();

            //测试数据为序号,航班号,起飞日期,三字码,起飞时刻,到达时刻,出发航站楼,到达航站楼,机型,飞行时间
            //1.3U8391   TU17NOV CTUCAI  0220 0715   T1 T2  330  10H55M
            DataTable dt = new DataTable();
            dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
            dt.Columns.Add("Date", typeof(string));//起飞日期
            dt.Columns.Add("Three", typeof(string));//三字码
            dt.Columns.Add("StartTime", typeof(string));//起飞时刻
            dt.Columns.Add("EndTime", typeof(string));//到达时刻
            dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
            dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
            dt.Columns.Add("AirModel", typeof(string)); //机型
            dt.Columns.Add("FlightTime", typeof(string));//飞行时间
            dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据 
            dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
            dt.Columns.Add("Error", typeof(string));//整理的到达日期
            dt.Columns.Add("Sign", typeof(string));//标识：0表示为原生黑屏代码、1表示“+1”新增的黑屏代码

            //添加转机标识
            dt.Columns.Add("isTransitShipment", typeof(bool));

            //判断是否录入黑屏代码
            if (listcode.Count == 0 || listcode == null)
            {
                dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "黑屏代码未录入!", null, false);
            }
            else
            {
                //读取单段黑屏代码
                for (int i = 0; i < listcode.Count; i++)
                {
                    //去除序号
                    string[] CodeList = Regex.Split(listcode[i].BlackCode, "\\d+\\.", RegexOptions.IgnoreCase);
                    //去除多余空格，方法一Linq扩展方法
                    CodeList = CodeList.Where(str => !string.IsNullOrWhiteSpace(str)).ToArray();

                    //读取单条黑屏代码
                    for (int j = 0; j < CodeList.Length; j++)
                    {
                        //去除多余空格，方法二使用Split()方法进行分割,分割有一个选项是RemoveEmptyEntries
                        CodeList[j] = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty).TrimStart().TrimEnd();
                        string[] Info = CodeList[j].Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                        //去除多余空格
                        Info = Info.Where(str => !string.IsNullOrWhiteSpace(str)).ToArray();

                        //判断黑屏代码是否正确拆分; 理应拆成9段
                        if (Info.TakeWhile((x) =>
                        {
                            return !excludeArr.Contains(x);
                        }).Count() != 9)
                        {
                            dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "本团组第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误,请联系机票同事核对", null, false);
                            //MessageBoxEx.Show("第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误, 请联系机票同事核对");
                            return dt;
                        }
                        else
                        {
                            try
                            {
                                var monthEn = Info[1].Substring(4, 3);

                                //月                  
                                int month = Convert.ToInt32(GetLonger(monthEn));
                                //日
                                int day = Convert.ToInt32(Info[1].Substring(2, 2));

                                var time = new DateTime(DateTime.Now.Year, month, day); //

                                var isExist = Info.Contains("[中转]");

                                //dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
                                //dt.Columns.Add("Date", typeof(string));//起飞日期
                                //dt.Columns.Add("Three", typeof(string));//三字码
                                //dt.Columns.Add("StartTime", typeof(string));//起飞时刻
                                //dt.Columns.Add("EndTime", typeof(string));//到达时刻
                                //dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
                                //dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
                                //dt.Columns.Add("AirModel", typeof(string)); //机型
                                //dt.Columns.Add("FlightTime", typeof(string));//飞行时间
                                //dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据 
                                //dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
                                //dt.Columns.Add("Error", typeof(string));//整理的到达日期
                                //dt.Columns.Add("Sign", typeof(string));//标识：0表示为原生黑屏代码、1表示“+1”新增的黑屏代码
                                dt.Rows.Add(Info[0],
                                       Info[1],
                                       Info[2],
                                       Info[3],
                                       Info[4],
                                       Info[5],
                                       Info[6],
                                       Info[7],
                                       Info[8],
                                       time.ToString("yyyy-MM-dd"),
                                       time.ToString("yyyy-MM-dd"),
                                       "",
                                       "0",
                                       isExist
                                       );

                            }
                            catch (Exception ex)
                            {
                                string exstr = ex.Message.ToString();
                            }
                        }

                    }

                    //排序
                    dt.DefaultView.Sort = "Day asc";
                    dt = dt.DefaultView.ToTable();
                }
            }

            return dt;
        }

        /// <summary>
        ///根据黑屏代码整理DataTable
        /// </summary>
        /// <param name="blackCode"></param>
        /// <returns></returns>
        public static DataTable GetTableByBlackCodeBySingle(Air_TicketBlackCode blackCode)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Fliagtcode", typeof(string)); //航班号
            dt.Columns.Add("Date", typeof(string));//起飞日期
            dt.Columns.Add("Three", typeof(string));//三字码
            dt.Columns.Add("StartTime", typeof(string));//起飞时刻
            dt.Columns.Add("EndTime", typeof(string));//到达时刻
            dt.Columns.Add("StartBuilding", typeof(string));//出发航站楼
            dt.Columns.Add("EndBuilding", typeof(string));//到达航站楼
            dt.Columns.Add("AirModel", typeof(string)); //机型
            dt.Columns.Add("FlightTime", typeof(string));//飞行时间
            dt.Columns.Add("Day", typeof(string));//整理的起飞日期;作为排序依据 
            dt.Columns.Add("ArrivedDate", typeof(string));//整理的到达日期
            dt.Columns.Add("Error", typeof(string));//整理的到达日期
            dt.Columns.Add("Sign", typeof(string));//标识：0表示为原生黑屏代码、1表示“+1”新增的黑屏代码

            //添加转机标识
            dt.Columns.Add("isTransitShipment", typeof(bool));
            //添加国内段标识
            dt.Columns.Add("isDomesticSection", typeof(bool));

            //判断是否录入黑屏代码
            if (blackCode == null)
            {
                dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "黑屏代码未录入!", null, false, false);
            }
            else
            {
                var listcode = new List<Air_TicketBlackCode> { blackCode };

                //读取单段黑屏代码
                for (int i = 0; i < listcode.Count; i++)
                {
                    //去除序号
                    string[] CodeList = Regex.Split(listcode[i].BlackCode, "\\d+\\.", RegexOptions.IgnoreCase);
                    //去除多余空格，方法一Linq扩展方法
                    CodeList = CodeList.Where(str => !string.IsNullOrWhiteSpace(str)).ToArray();

                    //读取单条黑屏代码
                    for (int j = 0; j < CodeList.Length; j++)
                    {
                        //去除多余空格，方法二使用Split()方法进行分割,分割有一个选项是RemoveEmptyEntries
                        CodeList[j] = CodeList[j].Replace("\r\n", string.Empty).Replace("\\r\\n", string.Empty).TrimStart().TrimEnd();
                        string[] Info = CodeList[j].Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

                        //去除多余空格
                        Info = Info.Where(str => !string.IsNullOrWhiteSpace(str)).ToArray();

                        //判断黑屏代码是否正确拆分; 理应拆成9段
                        if (Info.TakeWhile((x) =>
                        {
                            return !excludeArr.Contains(x);
                        }).Count() != 9)
                        {
                            dt.Rows.Add(null, null, null, null, null, null, null, null, null, null, null, "本团组第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误,请联系机票同事核对", null, false, false);
                            //MessageBoxEx.Show("第" + (i + 1) + "段黑屏代码中第" + (j + 1) + " 条有误, 请联系机票同事核对");
                            return dt;
                        }
                        else
                        {
                            try
                            {
                                var monthEn = Info[1].Substring(4, 3);

                                //月                  
                                int month = Convert.ToInt32(GetLonger(monthEn));
                                //日
                                int day = Convert.ToInt32(Info[1].Substring(2, 2));

                                var time = new DateTime(DateTime.Now.Year, month, day); //

                                var isExist = Info.Contains("[中转]");
                                if (!isExist)
                                {
                                    isExist = Info.Contains("[转机]");
                                }

                                var isDomesticSection = Info.Contains("[国内]");

                                if (isDomesticSection)
                                {
                                    continue;
                                }

                                dt.Rows.Add(Info[0],
                                       Info[1],
                                       Info[2],
                                       Info[3],
                                       Info[4],
                                       Info[5],
                                       Info[6],
                                       Info[7],
                                       Info[8],
                                       time.ToString("yyyy-MM-dd"),
                                       time.ToString("yyyy-MM-dd"),
                                       "",
                                       ExtractNumberAfterPlus(Info[4]),
                                       isExist,
                                       isDomesticSection
                                       );

                            }
                            catch (Exception ex)
                            {
                                string exstr = ex.Message.ToString();
                            }
                        }

                    }

                    //排序
                    dt.DefaultView.Sort = "Day asc";
                    dt = dt.DefaultView.ToTable();
                }
            }

            return dt;
        }

        private static int ExtractNumberAfterPlus(string input)
        {
            int value = 0;

            if (string.IsNullOrEmpty(input))
            {
                return value;
            }

            string pattern = @"\+\d+"; // 匹配“+”及其后的数字
            Match match = Regex.Match(input, pattern);

            if (match.Success)
            {
                int.TryParse(match.Value.Substring(1), out value); // 去掉“+”符号，只保留数字部分
            }

            return value;
        }

        /// <summary>
        /// 根据星期,月份的缩写,转换成数字或者全称
        /// 根据币种中文名称返回币种代码
        /// 根据数字返回机型型号【2、3开头的就是空客，比如空客320，7开头的就是波音，比如波音777】
        /// 20210903贾文滔
        /// </summary>
        /// <param name="temp"></param>
        /// <returns></returns>
        public static string GetLonger(string temp)
        {
            string str = "";
            switch (temp.ToUpper())
            {
                case "美元":
                    str = "USD";
                    break;
                case "日元":
                    str = "JPY";
                    break;
                case "英镑":
                    str = "GBP";
                    break;
                case "欧元":
                    str = "EUR";
                    break;
                case "港币":
                    str = "HKD";
                    break;
                case "MO":
                    str = "星期一";
                    break;
                case "TU":
                    str = "星期二";
                    break;
                case "WE":
                    str = "星期三";
                    break;
                case "TH":
                    str = "星期四";
                    break;
                case "FR":
                    str = "星期五";
                    break;
                case "SA":
                    str = "星期六";
                    break;
                case "SU":
                    str = "星期天";
                    break;
                case "JAN":
                    str = "01";
                    break;
                case "FEB":
                    str = "02";
                    break;
                case "MAR":
                    str = "03";
                    break;
                case "APR":
                    str = "04";
                    break;
                case "MAY":
                    str = "05";
                    break;
                case "JUN":
                    str = "06";
                    break;
                case "JUL":
                    str = "07";
                    break;
                case "AUG":
                    str = "08";
                    break;
                case "SEP":
                    str = "09";
                    break;
                case "OCT":
                    str = "10";
                    break;
                case "NOV":
                    str = "11";
                    break;
                case "DEC":
                    str = "12";
                    break;
                case "MONDAY":
                    str = "星期一";
                    break;
                case "TUESDAY":
                    str = "星期二";
                    break;
                case "WEDNESDAY":
                    str = "星期三";
                    break;
                case "THURSDAY":
                    str = "星期四";
                    break;
                case "FRIDAY":
                    str = "星期五";
                    break;
                case "SATURDAY":
                    str = "星期六";
                    break;
                case "SUNDAY":
                    str = "星期日";
                    break;
                case "01":
                    str = "JAN";
                    break;
                case "02":
                    str = "FEB";
                    break;
                case "03":
                    str = "MAR";
                    break;
                case "04":
                    str = "APR";
                    break;
                case "05":
                    str = "MAY";
                    break;
                case "06":
                    str = "JUN";
                    break;
                case "07":
                    str = "JUL";
                    break;
                case "08":
                    str = "AUG";
                    break;
                case "09":
                    str = "SEP";
                    break;
                case "10":
                    str = "OCT";
                    break;
                case "11":
                    str = "NOV";
                    break;
                case "12":
                    str = "DEC";
                    break;
                case "2":
                    str = "空客A";
                    break;
                case "3":
                    str = "空客A";
                    break;
                case "7":
                    str = "波音";
                    break;
            }

            return str;
        }

        /// <summary>
        /// 根据月份返回天数
        /// </summary>
        /// <param name="Month"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public static string GetDaysByMonth(string Month, int year)
        {
            string str = "";
            //判断是否是闰年
            if (DateTime.IsLeapYear(year) == false)
            {
                switch (Month.ToUpper())
                {
                    case "JAN":
                        str = "31";
                        break;
                    case "FEB":
                        str = "28";
                        break;
                    case "MAR":
                        str = "31";
                        break;
                    case "APR":
                        str = "30";
                        break;
                    case "MAY":
                        str = "31";
                        break;
                    case "JUN":
                        str = "30";
                        break;
                    case "JUL":
                        str = "31";
                        break;
                    case "AUG":
                        str = "31";
                        break;
                    case "SEP":
                        str = "30";
                        break;
                    case "OCT":
                        str = "31";
                        break;
                    case "NOV":
                        str = "30";
                        break;
                    case "DEC":
                        str = "31";
                        break;
                    case "01":
                        str = "31";
                        break;
                    case "02":
                        str = "28";
                        break;
                    case "03":
                        str = "31";
                        break;
                    case "04":
                        str = "30";
                        break;
                    case "05":
                        str = "31";
                        break;
                    case "06":
                        str = "30";
                        break;
                    case "07":
                        str = "31";
                        break;
                    case "08":
                        str = "31";
                        break;
                    case "09":
                        str = "30";
                        break;
                    case "10":
                        str = "31";
                        break;
                    case "11":
                        str = "30";
                        break;
                    case "12":
                        str = "31";
                        break;
                }
            }
            else
            {
                switch (Month.ToUpper())
                {
                    case "JAN":
                        str = "31";
                        break;
                    case "FEB":
                        str = "29";
                        break;
                    case "MAR":
                        str = "31";
                        break;
                    case "APR":
                        str = "30";
                        break;
                    case "MAY":
                        str = "31";
                        break;
                    case "JUN":
                        str = "30";
                        break;
                    case "JUL":
                        str = "31";
                        break;
                    case "AUG":
                        str = "31";
                        break;
                    case "SEP":
                        str = "30";
                        break;
                    case "OCT":
                        str = "31";
                        break;
                    case "NOV":
                        str = "30";
                        break;
                    case "DEC":
                        str = "31";
                        break;
                    case "01":
                        str = "31";
                        break;
                    case "02":
                        str = "29";
                        break;
                    case "03":
                        str = "31";
                        break;
                    case "04":
                        str = "30";
                        break;
                    case "05":
                        str = "31";
                        break;
                    case "06":
                        str = "30";
                        break;
                    case "07":
                        str = "31";
                        break;
                    case "08":
                        str = "31";
                        break;
                    case "09":
                        str = "30";
                        break;
                    case "10":
                        str = "31";
                        break;
                    case "11":
                        str = "30";
                        break;
                    case "12":
                        str = "31";
                        break;
                }
            }
            return str;
        }

        /// <summary>
        /// op行程单 团组 城市路径
        /// </summary>
        /// <param name="diid">团组Id</param>
        /// <param name="separator">分隔符</param>
        /// <returns></returns>
        public static string GetGroupCityLine(int diid, string separator)
        {
            string city = string.Empty;
            var blackCode = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.IsDel == 0 && x.DiId == diid).ToList();
            if (blackCode.Count > 0)
            {
                var black = blackCode.First();
                black.BlackCode = black.BlackCode == null ? "" : black.BlackCode;
                var blackSp = Regex.Split(black.BlackCode, "\\d+\\.", RegexOptions.IgnoreCase).Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
                if (blackSp.Length > 0)
                {
                    try
                    {
                        var cityArrCode = new List<string>(20);
                        foreach (var item in blackSp)
                        {
                            var itemSp = item.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
                            var IndexSelect = itemSp[2];
                            var cityArrCodeLength = cityArrCode.Count - 1;
                            var startCity = IndexSelect.Substring(0, 3);
                            if (cityArrCodeLength > 0)
                            {
                                var arrEndCity = cityArrCode[cityArrCodeLength];
                                if (arrEndCity != startCity)
                                {
                                    cityArrCode.Add(startCity.ToUpper());
                                }
                            }
                            else
                            {
                                cityArrCode.Add(startCity.ToUpper());
                            }

                            var endCity = IndexSelect.Substring(3, 3);
                            cityArrCode.Add(endCity.ToUpper());
                        }

                        var cityThree = string.Empty;
                        cityArrCode.ForEach(x => cityThree += "'" + x + "',");
                        cityThree = cityThree.TrimEnd(',');
                        if (string.IsNullOrWhiteSpace(cityThree))
                        {
                            throw new Exception("error");
                        }

                        string sql = $"SELECT * FROM Res_ThreeCode rtc WHERE UPPER(rtc.Three) in ({cityThree}) ";
                        var cityArr = _dirRep._sqlSugar.SqlQueryable<Res_ThreeCode>(sql).ToList();
                        foreach (var item in cityArrCode)
                        {
                            var find = cityArr.Find(x => x.Three.ToUpper() == item.ToUpper());
                            if (find != null)
                            {
                                city += find.City + separator;
                            }
                            else
                            {
                                city += item + "[三字码未收录]" + separator;
                            }
                        }
                        city = city.TrimEnd(char.Parse(separator));
                    }
                    catch (Exception e)
                    {
                        city = "[黑屏代码格式不正确!]";
                    }
                }
            }
            else city = "[未录入黑屏代码]";

            return city;
        }


        /// <summary>
        /// op行程单 团组 城市路径
        /// </summary>
        /// <param name="black">黑屏代码实例</param>
        /// <param name="separator">分隔符</param>
        /// <returns></returns>
        public static string GetGroupCityLine(Air_TicketBlackCode black, string separator)
        {
            string city = string.Empty;
            if (black != null)
            {
                black.BlackCode = black.BlackCode == null ? "" : black.BlackCode;
                var blackSp = Regex.Split(black.BlackCode, "\\d+\\.", RegexOptions.IgnoreCase).Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
                if (blackSp.Length > 0)
                {
                    try
                    {
                        var cityArrCode = new List<string>(20);
                        foreach (var item in blackSp)
                        {
                            var itemSp = item.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
                            var IndexSelect = itemSp[2];
                            var cityArrCodeLength = cityArrCode.Count - 1;
                            var startCity = IndexSelect.Substring(0, 3);
                            if (cityArrCodeLength > 0)
                            {
                                var arrEndCity = cityArrCode[cityArrCodeLength];
                                if (arrEndCity != startCity)
                                {
                                    cityArrCode.Add(startCity.ToUpper());
                                }
                            }
                            else
                            {
                                cityArrCode.Add(startCity.ToUpper());
                            }

                            var endCity = IndexSelect.Substring(3, 3);
                            cityArrCode.Add(endCity.ToUpper());
                        }

                        var cityThree = string.Empty;
                        cityArrCode.ForEach(x => cityThree += "'" + x + "',");
                        cityThree = cityThree.TrimEnd(',');
                        if (string.IsNullOrWhiteSpace(cityThree))
                        {
                            throw new Exception("error");
                        }

                        string sql = $"SELECT * FROM Res_ThreeCode rtc WHERE UPPER(rtc.Three) in ({cityThree}) ";
                        var cityArr = _dirRep._sqlSugar.SqlQueryable<Res_ThreeCode>(sql).ToList();
                        foreach (var item in cityArrCode)
                        {
                            var find = cityArr.Find(x => x.Three.ToUpper() == item.ToUpper());
                            if (find != null)
                            {
                                city += find.City + separator;
                            }
                            else
                            {
                                city += item + "[三字码未收录]" + separator;
                            }
                        }
                        city = city.TrimEnd(char.Parse(separator));
                    }
                    catch (Exception e)
                    {
                        city = "[黑屏代码格式不正确!]";
                    }
                }
            }
            else city = "[黑屏代码有误！]";
            return city;
        }


        /// <summary>
        /// op行程单 团组 城市路径
        /// </summary>
        /// <param name="diids">团组Id</param>
        /// <param name="separator">分隔符</param>
        /// <returns></returns>
        public static Dictionary<int, string> GetGroupCityLineItem(List<int> diids, string separator)
        {
            Dictionary<int, string> dicCitys = new Dictionary<int, string>();
            var cityCodes = _dirRep._sqlSugar.Queryable<Res_ThreeCode>().Where(it => it.IsDel == 0).ToList();
            var blackCodes = _dirRep._sqlSugar.Queryable<Air_TicketBlackCode>().Where(x => x.IsDel == 0 && diids.Contains(x.DiId)).ToList();

            foreach (var diid in diids)
            {
                string city = string.Empty;
                var blackCode = blackCodes.Where(it => it.DiId == diid).ToList();
                if (blackCode.Count > 0)
                {
                    var black = blackCode.First();
                    black.BlackCode = black.BlackCode == null ? "" : black.BlackCode;
                    var blackSp = Regex.Split(black.BlackCode, "\\d+\\.", RegexOptions.IgnoreCase).Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();
                    if (blackSp.Length > 0)
                    {
                        try
                        {
                            var cityArrCode = new List<string>(20);
                            foreach (var item in blackSp)
                            {
                                var itemSp = item.Split(' ').Where(x => !string.IsNullOrWhiteSpace(x)).ToList();
                                var IndexSelect = itemSp[2];
                                var cityArrCodeLength = cityArrCode.Count - 1;
                                var startCity = IndexSelect.Substring(0, 3);
                                if (cityArrCodeLength > 0)
                                {
                                    var arrEndCity = cityArrCode[cityArrCodeLength];
                                    if (arrEndCity != startCity)
                                    {
                                        cityArrCode.Add(startCity.ToUpper());
                                    }
                                }
                                else
                                {
                                    cityArrCode.Add(startCity.ToUpper());
                                }

                                var endCity = IndexSelect.Substring(3, 3);
                                cityArrCode.Add(endCity.ToUpper());
                            }

                            var cityThree = string.Empty;
                            cityArrCode.ForEach(x => cityThree += "'" + x + "',");
                            cityThree = cityThree.TrimEnd(',');
                            if (string.IsNullOrWhiteSpace(cityThree))
                            {
                                throw new Exception("error");
                            }

                            var cityArr = cityCodes.Where(it => cityThree.Contains(it.Three.ToUpper())).ToList();
                            foreach (var item in cityArrCode)
                            {
                                var find = cityArr.Find(x => x.Three.ToUpper() == item.ToUpper());
                                if (find != null)
                                {
                                    city += find.City + separator;
                                }
                                else
                                {
                                    city += item + "[三字码未收录]" + separator;
                                }
                            }
                            city = city.TrimEnd(char.Parse(separator));
                        }
                        catch (Exception e)
                        {
                            city = "[黑屏代码格式不正确!]";
                        }
                    }
                }
                else city = "[未录入黑屏代码]";

                dicCitys.Add(diid, city);
            }



            return dicCitys;
        }


        /// <summary>
        /// op行程单相关团组信息 含途径城市
        /// </summary>
        /// <returns></returns>
        public static async Task<List<MateOpGroupPageListView>> MateOpGroupPageListRedis()
        {
            List<MateOpGroupPageListView> _views = new List<MateOpGroupPageListView>();
            string viewStr = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>("MateOpGroupPageList");//string 取
            if (!string.IsNullOrEmpty(viewStr))
            {
                _views = JsonConvert.DeserializeObject<List<MateOpGroupPageListView>>(viewStr);
            }
            else
            {
                string sql = string.Format($@"Select Row_Number,Id,SalesQuoteNo,TourCode,TeamTypeId, TeamType,
                                             	TeamLevId,TeamLev,TeamName,ClientName,ClientUnit,
                                             	VisitDate,VisitDays,VisitPNumber,JietuanOperatorId,
                                             	JietuanOperator,IsSure,CreateTime,VisitCountry
                                             	From (
                                             	Select row_number() over(order by gdi.CreateTime Desc) as Row_Number,
                                             	gdi.Id,SalesQuoteNo,TourCode,ssd.Id TeamTypeId, ssd.Name TeamType,
                                             	ssd1.Id TeamLevId,ssd1.Name TeamLev,TeamName,ClientName,ClientUnit,
                                             	VisitDate,VisitDays,VisitPNumber,JietuanOperator JietuanOperatorId,
                                             	su.CnName JietuanOperator,IsSure,gdi.CreateTime,gdi.VisitCountry
                                             	From  Grp_DelegationInfo gdi
                                             	Left Join Sys_SetData ssd On gdi.TeamDid = ssd.Id 
                                             	Left Join Sys_SetData ssd1 On gdi.TeamLevSId = ssd1.Id
                                             	Left Join Sys_Users su On gdi.JietuanOperator = su.Id
                                             	Where gdi.IsDel = 0 
                                             ) temp");

                _views = _dirRep._sqlSugar.SqlQueryable<MateOpGroupPageListView>(sql).ToList();

                #region 处理所属部门
                /*
                 * 1.sq 和 gyy 等显示 市场部
                 * 2.王鸽和主管及张总还有管理员号统一国交部
                 * 2-1.  4	管理员 ,21	张海麟
                 */
                List<int> userIds = _views.Select(it => it.JietuanOperatorId).ToList();
                List<int> userIds1 = new List<int>() { 4, 21 };
                var UserDepDatas = _dirRep._sqlSugar.Queryable<Sys_Users>()
                                          .LeftJoin<Sys_Department>((u, d) => u.DepId == d.Id)
                                          .Where(u => u.IsDel == 0 && userIds.Contains(u.Id))
                                          .Select((u, d) => new { UserId = u.Id, DepName = userIds1.Contains(u.Id) ? "国交部" : d.DepName })
                                          .ToList();

                #endregion

                _views.ForEach(it =>
                {
                    it.RouteCity = GetGroupCityLine(it.Id, @"/");
                    it.Department = UserDepDatas.Find(it1 => it.JietuanOperatorId == it1.UserId)?.DepName ?? "Unknown";
                });
                TimeSpan ts = DateTime.Now.AddHours(2).TimeOfDay;
                await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync<string>("MateOpGroupPageList", JsonConvert.SerializeObject(_views), ts);//string 存
            }

            return _views;
        }

        /// <summary>
        /// 获取黑屏代码日期列表
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<string> GetTimeListByDataTable(DataTable dt)
        {
            DateTime datestart = Convert.ToDateTime(dt.Rows[0]["Day"].ToString());
            DateTime dateend = Convert.ToDateTime(dt.Rows[dt.Rows.Count - 1]["ArrivedDate"].ToString());

            List<string> timeList = new List<string>();
            while (datestart <= dateend)
            {
                timeList.Add(datestart.ToString("yyyy-MM-dd"));
                datestart = datestart.AddDays(1);
            }
            return timeList;
        }

        const decimal conversion = 1.61M;

        /// <summary>
        /// 简要行程
        /// </summary>
        /// <param name="diid"></param>
        /// <returns></returns>
        public static Result GetBriefStroke(int diid)
        {
            DataTable resultTable = GetTableByBlackCode(diid);

            if (resultTable == null)
            {
                return new Result { Code = -1, Msg = "黑屏代码有误或黑屏代码未录入" };
            }

            if (resultTable.Rows.Count == 0 || string.IsNullOrWhiteSpace(resultTable.Rows[0][1].ToString()))
            {
                string msg = string.Empty;
                if (resultTable.Rows.Count > 0)
                {
                    msg = resultTable.Rows[0]["Error"].ToString();
                }
                return new Result { Code = -1, Msg = $"黑屏代码有误；{msg}" };

            }


            var timeArr = GetTimeListByDataTable(resultTable);
            var threeCodes = _dirRep._sqlSugar.Queryable<Res_ThreeCode>().Where(it => it.IsDel == 0).ToList();   //三字码
            var airCompanys = _dirRep._sqlSugar.Queryable<Res_AirCompany>().Where(it => it.IsDel == 0).ToList(); //航司公司
            var obDatas = _dirRep._sqlSugar.Queryable<Res_OfficialActivities>().Where(it => it.IsDel == 0 && it.DiId == diid).ToList(); //公务信息

            var NewListTravel = new List<Grp_TravelList>();
            var index = 0;
            var stopCity = string.Empty;

            foreach (var item in timeArr)
            {
                string trip = string.Empty;
                string weekDay = string.Empty;
                DateTime time = DateTime.Now;
                if (DateTime.TryParse(item, out time))
                {
                    weekDay = weekdays[(int)time.DayOfWeek];
                }
                else
                {
                    weekDay = "日期格式不正确!";
                }

                var empty = "【未收入该三字码！请机票同事录入】";
                var tabSelect = resultTable.Select(string.Format("Day = '{0}'", item));
                var isMoreTraffic = false;
                if (tabSelect.Length > 0)
                {
                    isMoreTraffic = true;
                    var takeOffTime = DateTime.Parse(item);
                    var fallToTime = DateTime.Parse(item);

                    Res_ThreeCode start_Object = null;
                    Res_ThreeCode end_Object = null;

                    //bool isTrade = false;

                    //air 处理
                    foreach (var tabRow in tabSelect)
                    {

                        takeOffTime = DateTime.Parse(item);
                        fallToTime = DateTime.Parse(item);

                        var takeOff = tabRow["StartTime"].ToString();
                        var fallTo = tabRow["EndTime"].ToString();

                        takeOffTime = takeOffTime.AddHours(int.Parse(takeOff.Substring(0, 2)));
                        takeOffTime = takeOffTime.AddMinutes(int.Parse(takeOff.Substring(2, 2)));

                        fallToTime = fallToTime.AddHours(int.Parse(fallTo.Substring(0, 2)));
                        fallToTime = fallToTime.AddMinutes(int.Parse(fallTo.Substring(2, 2)));

                        var threeCode = tabRow["Three"].ToString();
                        var start = threeCode.Substring(0, 3);
                        var end = threeCode.Substring(3, 3);
                        stopCity = end;
                        start_Object = threeCodes.Find(x => x.Three.ToUpper() == start.ToUpper());
                        end_Object = threeCodes.Find(x => x.Three.ToUpper() == end.ToUpper());
                        if (start_Object == null)
                        {
                            start_Object = new Res_ThreeCode()
                            {
                                AirPort = empty,
                                AirPort_En = empty,
                                City = empty,
                                Country = empty,
                                Four = empty,
                                Three = empty,
                            };
                        }
                        if (end_Object == null)
                        {
                            end_Object = new Res_ThreeCode()
                            {
                                AirPort = empty,
                                AirPort_En = empty,
                                City = empty,
                                Country = empty,
                                Four = empty,
                                Three = empty,
                            };
                        }

                        //航班号
                        string flightcode = tabRow["Fliagtcode"].ToString();
                        trip += $"{takeOffTime.ToString("HH:mm")}—{fallToTime.ToString("HH:mm")} {start_Object.City}—{end_Object.City} 航班号：{flightcode}\r\n";
                    }

                    //var airArrive = fallToTime;      //航班落地时间
                    time = fallToTime.AddHours(1.5); //出机场一个半小时

                    string Time = string.Empty;
                    string Distance = string.Empty;
                    int GetGoogleResult = 0;
                    int GetDistResult = 0;
                    JObject Result = null;
                    try
                    {
                        Time = Result["routes"][0]["legs"][0]["duration"]["text"].ToString().Replace(" ", "").Trim();
                        Distance = Result["routes"][0]["legs"][0]["distance"]["text"].ToString().Replace(" ", "").Trim();

                        if (Time.Contains("hours"))
                        {
                            Time = Time.Replace("hours", "小时");
                        }
                        if (Time.Contains("hour"))
                        {
                            Time = Time.Replace("hour", "小时");
                        }
                        Time = Time.Replace("mins", "分钟");

                        if (Distance.Contains("mi"))
                        {
                            var distSp = Regex.Split(Distance, "mi");
                            if (distSp.Length > 0)
                            {
                                Distance = (decimal.Parse(distSp[0]) * conversion).ToString("#0.00") + " 公里";
                            }
                        }
                        if (Distance.Contains("km"))
                        {
                            Distance = Distance.Replace("km", "公里");
                        }

                        if (Time.Contains("小时"))
                        {
                            var xs = Regex.Split(Time, "小时");
                            var xsValue = int.Parse(xs[0]);
                            var fz = Regex.Split(xs[1], "分钟");
                            var fzValue = int.Parse(fz[0]);
                            GetGoogleResult = xsValue * 60;
                            GetGoogleResult += fzValue;

                        }
                        else if (Time.Contains("分钟"))
                        {
                            GetGoogleResult = int.Parse(Regex.Split(Time, "分钟")[0]);
                        }
                        if (Distance.Contains("公里"))
                        {
                            GetDistResult = (int)decimal.Parse(Regex.Split(Distance, "公里")[0]);
                        }

                        if (GetGoogleResult > 0 && GetGoogleResult % 5 != 0)
                        {
                            while (GetGoogleResult % 5 != 0)
                            {
                                GetGoogleResult++;
                            }
                        }
                        if (GetDistResult % 5 != 0 && GetDistResult > 0)
                        {
                            while (GetDistResult % 5 != 0)
                            {
                                GetDistResult++;
                            }
                        }

                        int H = (GetGoogleResult / 60);
                        int m = (GetGoogleResult % 60);
                        string TimeStr = string.Empty;
                        if (H != 0)
                        {
                            if (H < 10)
                            {
                                TimeStr += "0" + H + "小时";
                            }
                            else
                            {
                                TimeStr += H + "小时";
                            }
                        }
                        if (m < 10)
                        {
                            TimeStr += "0" + m + "分钟";
                        }
                        else
                        {
                            TimeStr += m + "分钟";
                        }

                        Time = TimeStr;
                        Distance = GetDistResult.ToString() + "公里";
                    }
                    catch (Exception ex)
                    {
                        Time = "未知！";
                        Distance = "未知！";
                    }

                    //trip += $"{time.ToString("HH:mm")} 搭乘专车前往市区（{end_Object.AirPort} - {end_Object.City}市区 路程{Distance}，耗时{Time}）";
                    //trip += $"{time.ToString("HH:mm")} 搭乘专车前往市区（{end_Object.AirPort} - {end_Object.City}市区）";

                    time = time.AddMinutes(GetGoogleResult);  //到达市区时间

                    string tripDate = Convert.ToDateTime(item).ToString("M月d日");
                    if (index == timeArr.Count - 1)
                    {
                        NewListTravel.Add(new Grp_TravelList
                        {
                            CreateTime = DateTime.Now,
                            CreateUserId = 0,
                            Diid = diid,
                            Date = tripDate,
                            Trip = trip,
                            WeekDay = weekDay,
                            Days = index + 1,
                            Traffic_First = "飞机",
                            Traffic_Second = "汽车",
                            Diffgroup = 1,
                            Issel = 1,
                            IsDel = 0,
                        });
                        continue;
                    }

                    if (time.Day != fallToTime.Day)  //超出一天
                    {
                        NewListTravel.Add(new Grp_TravelList
                        {
                            CreateTime = DateTime.Now,
                            CreateUserId = 0,
                            Diid = diid,
                            Date = tripDate,
                            Trip = trip,
                            WeekDay = weekDay,
                            Days = index + 1,
                            Traffic_First = "飞机",
                            Traffic_Second = "汽车",
                            Diffgroup = 1,
                            Issel = 1,
                            IsDel = 0,
                        });
                        continue;
                    }

                    var obInfo = obDatas.Find(it => Convert.ToDateTime(it.Date).ToString("M月d日").Equals(tripDate));
                    string obtime = "";
                    string obcontent = "";

                    if (obInfo != null)
                    {
                        obtime = obInfo.Time;
                        obcontent = $"拜访{obInfo.Client ?? "公务出访单位未录入"}{obInfo.Job ?? "公务出访联系人职务未录入"}{obInfo.Contact ?? "公务出访联系人职务未录入"}";
                    }

                    bool obTime_bool = DateTime.TryParse(obtime, out DateTime obTimeDt);

                    if (time.Hour < 9)  // && (airArrive < new DateTime(airArrive.Year,airArrive.Month,airArrive.Day,6, 30, 0))
                    {
                        if (obTime_bool)
                        {
                            if (obTimeDt.Hour < 9)
                            {
                                trip += $"{obTimeDt.ToString("HH:mm")}—10:30 {obcontent}；\r\n";
                            }
                        }
                        else
                        {
                            trip += "09:00—10:30 公务活动；\r\n";
                        }
                    }
                    else if (time.Hour < 10)
                    {
                        if (obTime_bool)
                        {
                            if (obTimeDt.Hour < 10)
                            {
                                trip += $"{obTimeDt.ToString("HH:mm")}—12:00 {obcontent}；\r\n";
                            }
                        }
                        else
                        {
                            trip += "10:30—12:00 公务活动；\r\n";
                        }
                    }

                    if (time.Hour < 13)
                    {
                        //trip += $"\r\n{time.ToString("HH:mm")}         午餐于当地餐厅;";
                    }

                    if (time < new DateTime(time.Year, time.Month, time.Day, 14, 30, 0))
                    {
                        if (obTime_bool)
                        {
                            if (obTimeDt < new DateTime(time.Year, time.Month, time.Day, 14, 30, 0))
                            {
                                trip += $"{obTimeDt.ToString("HH:mm")}—16:00 {obcontent}；\r\n";
                            }
                        }
                        else
                        {
                            trip += "14:00—16:00 公务活动；\r\n";
                        }
                    }
                    else if (time.Hour < 16)
                    {
                        if (obTime_bool)
                        {
                            if (obTimeDt.Hour < 16)
                            {
                                trip += $"{obTimeDt.ToString("HH:mm")}—17:30 {obcontent}；\r\n";
                            }
                        }
                        else
                        {
                            trip += "16:00—17:30 公务活动；\r\n";
                        }


                    }

                    //if (time.Hour < 18)
                    //{
                    //    trip += $"\r\n18:00        晚餐于当地餐厅;";
                    //}

                    //time = time.AddHours(1);
                    //trip += $"\r\n{time.ToString("HH:mm")} 搭乘专车前往酒店，抵达后办理入住；";
                }
                else
                {
                    var end_Object = threeCodes.Find(x => x.Three.ToUpper() == stopCity.ToUpper());
                    if (end_Object == null)
                    {
                        end_Object = new Res_ThreeCode()
                        {
                            AirPort = empty,
                            AirPort_En = empty,
                            City = empty,
                            Country = empty,
                            Four = empty,
                            Three = empty,
                        };
                    }
                    //trip += $"{end_Object.City}\r\n";
                    //                    trip += @"08:00        早餐于酒店；
                    //09:00—10:30 公务活动；
                    //12:00        午餐于当地餐厅；
                    //14:00—16:00 公务活动；
                    //18:00        晚餐于当地餐厅；
                    //19:00        入住酒店休息；";
                    trip += "09:00—10:30 公务活动；\r\n14:00—16:00 公务活动；\r\n";

                }

                string[] traffic = new string[] { "飞机", "汽车" };
                if (!isMoreTraffic)
                {
                    traffic = new string[] { "汽车", "" };
                }

                NewListTravel.Add(new Grp_TravelList
                {
                    CreateTime = DateTime.Now,
                    CreateUserId = 0,
                    Diid = diid,
                    Date = Convert.ToDateTime(item).ToString("M月d日"),
                    Trip = trip,
                    WeekDay = weekDay,
                    Days = index + 1,
                    Traffic_First = traffic[0],
                    Traffic_Second = traffic[1],
                    Diffgroup = 1,
                    Issel = 1,
                    IsDel = 0,
                });

                index++;
            }

            return new Result() { Code = 0, Msg = "获取成功!", Data = NewListTravel };
        }

        /// <summary>
        /// 根据日期获取星期
        /// </summary>
        /// <param name="dtStr"></param>
        /// <returns></returns>
        public static string GetWeek(this string dtStr)
        {
            string week = "";
            bool isDt = DateTime.TryParse(dtStr, out DateTime dt);
            if (isDt)
            {
                week = weekdays[(int)dt.DayOfWeek];
            }

            return week;
        }
        #endregion

        #region 计算国家城市当时时间

        /// <summary>
        /// 中文城市名称映射
        /// </summary>
        private static readonly Dictionary<string, string> _timeZoneCityMappings = new Dictionary<string, string>
        {
{"Africa/Abidjan","非洲/阿比让"}                                         ,
{"Africa/Accra","非洲/阿克拉"}                                           ,
{"Africa/Addis_Ababa","非洲/亚的斯亚贝巴"}                               ,
{"Africa/Algiers","非洲/阿尔及尔"}                                       ,
{"Africa/Asmara","非洲/阿斯马拉"}                                        ,
{"Africa/Asmera","非洲/阿斯梅拉"}                                        ,
{"Africa/Bamako","非洲/巴马科"}                                          ,
{"Africa/Bangui","非洲/班吉"}                                            ,
{"Africa/Banjul","非洲/班珠尔"}                                          ,
{"Africa/Bissau","非洲/比绍"}                                            ,
{"Africa/Blantyre","非洲/布兰太尔"}                                      ,
{"Africa/Brazzaville","非洲/布拉柴维尔"}                                 ,
{"Africa/Bujumbura","非洲/布琼布拉"}                                     ,
{"Africa/Cairo","非洲/开罗"}                                             ,
{"Africa/Casablanca","非洲/卡萨布兰卡"}                                  ,
{"Africa/Ceuta","非洲/休达"}                                             ,
{"Africa/Conakry","非洲/科纳克里"}                                       ,
{"Africa/Dakar","非洲/达喀尔"}                                           ,
{"Africa/Dar_es_Salaam","非洲/达累斯萨拉姆"}                             ,
{"Africa/Djibouti","非洲/吉布提"}                                        ,
{"Africa/Douala","非洲/杜阿拉"}                                          ,
{"Africa/El_Aaiun","非洲/阿尤恩"}                                        ,
{"Africa/Freetown","非洲/弗里敦"}                                        ,
{"Africa/Gaborone","非洲/哈博罗内"}                                      ,
{"Africa/Harare","非洲/哈拉雷"}                                          ,
{"Africa/Johannesburg","非洲/约翰内斯堡"}                                ,
{"Africa/Juba","非洲/朱巴"}                                              ,
{"Africa/Kampala","非洲/坎帕拉"}                                         ,
{"Africa/Khartoum","非洲/喀土穆"}                                        ,
{"Africa/Kigali","非洲/基加利"}                                          ,
{"Africa/Kinshasa","非洲/金沙萨"}                                        ,
{"Africa/Lagos","非洲/拉各斯"}                                           ,
{"Africa/Libreville","非洲/利伯维尔"}                                    ,
{"Africa/Lome","非洲/洛美"}                                              ,
{"Africa/Luanda","非洲/罗安达"}                                          ,
{"Africa/Lubumbashi","非洲/卢本巴希"}                                    ,
{"Africa/Lusaka","非洲/卢萨卡"}                                          ,
{"Africa/Malabo","非洲/马拉博"}                                          ,
{"Africa/Maputo","非洲/马普托"}                                          ,
{"Africa/Maseru","非洲/马塞卢"}                                          ,
{"Africa/Mbabane","非洲/姆巴巴内"}                                       ,
{"Africa/Mogadishu","非洲/摩加迪沙"}                                     ,
{"Africa/Monrovia","非洲/蒙罗维亚"}                                      ,
{"Africa/Nairobi","非洲/内罗毕"}                                         ,
{"Africa/Ndjamena","非洲/恩贾梅纳"}                                      ,
{"Africa/Niamey","非洲/尼亚美"}                                          ,
{"Africa/Nouakchott","非洲/努瓦克肖特"}                                  ,
{"Africa/Ouagadougou","非洲/瓦加杜古"}                                   ,
{"Africa/Porto-Novo","非洲/波多诺伏"}                                    ,
{"Africa/Sao_Tome","非洲/圣多美"}                                        ,
{"Africa/Timbuktu","非洲/廷巴克图"}                                      ,
{"Africa/Tripoli","非洲/的黎波里"}                                       ,
{"Africa/Tunis","非洲/突尼斯"}                                           ,
{"Africa/Windhoek","非洲/温得和克"}                                      ,
{"America/Adak","美洲/埃达克"}                                           ,
{"America/Anchorage","美洲/安克雷奇"}                                    ,
{"America/Anguilla","美洲/安圭拉"}                                       ,
{"America/Antigua","美洲/安提瓜岛"}                                      ,
{"America/Araguaina","美洲/阿拉瓜纳"}                                    ,
{"America/Argentina/Buenos_Aires","美洲/阿根廷/布宜诺斯艾利斯"}          ,
{"America/Argentina/Catamarca","美洲/阿根廷/卡塔马卡"}                   ,
{"America/Argentina/ComodRivadavia","美洲/阿根廷/科莫德里瓦达维亚"}      ,
{"America/Argentina/Cordoba","美洲/阿根廷/科尔多瓦"}                     ,
{"America/Argentina/Jujuy","美洲/阿根廷/胡胡伊省"}                       ,
{"America/Argentina/La_Rioja","美洲/阿根廷/拉里奥哈"}                    ,
{"America/Argentina/Mendoza","美洲/阿根廷/门多萨"}                       ,
{"America/Argentina/Rio_Gallegos","美洲/阿根廷/里奥加耶戈斯"}            ,
{"America/Argentina/Salta","美洲/阿根廷/萨尔塔"}                         ,
{"America/Argentina/San_Juan","美洲/阿根廷/San_Juan"}                    ,
{"America/Argentina/San_Luis","美洲/阿根廷/圣路易斯"}                    ,
{"America/Argentina/Tucuman","美洲/阿根廷/图库曼"}                       ,
{"America/Argentina/Ushuaia","美洲/阿根廷/乌斯怀亚"}                     ,
{"America/Aruba","美洲/阿鲁巴岛"}                                        ,
{"America/Asuncion","美洲/亚松森"}                                       ,
{"America/Atikokan","美洲/阿蒂科坎"}                                     ,
{"America/Atka","美洲/阿特卡"}                                           ,
{"America/Bahia","美洲/巴伊亚"}                                          ,
{"America/Bahia_Banderas","美洲/巴伊亚_班德拉斯"}                        ,
{"America/Barbados","美洲/巴巴多斯"}                                     ,
{"America/Belem","美洲/贝伦"}                                            ,
{"America/Belize","美洲/伯利兹"}                                         ,
{"America/Blanc-Sablon","America/Blanc-Sablon"}                          ,
{"America/Boa_Vista","美洲/博阿维斯塔"}                                  ,
{"America/Bogota","美洲/波哥大"}                                         ,
{"America/Boise","美洲/博伊西"}                                          ,
{"America/Buenos_Aires","美洲/布宜诺斯艾利斯"}                           ,
{"America/Cambridge_Bay","美洲/剑桥湾"}                                  ,
{"America/Campo_Grande","美洲/大坎普"}                                   ,
{"America/Cancun","美洲/坎昆"}                                           ,
{"America/Caracas","美洲/加拉加斯"}                                      ,
{"America/Catamarca","美洲/卡塔马卡"}                                    ,
{"America/Cayenne","美洲/卡宴"}                                          ,
{"America/Cayman","美洲/开曼"}                                           ,
{"America/Chicago","美洲/芝加哥"}                                        ,
{"America/Chihuahua","美洲/奇瓦瓦州"}                                    ,
{"America/Ciudad_Juarez","美洲/华雷斯城"}                                ,
{"America/Coral_Harbour","美洲/珊瑚港"}                                  ,
{"America/Cordoba","美洲/科尔多瓦"}                                      ,
{"America/Costa_Rica","美洲/哥斯达黎加"}                                 ,
{"America/Creston","美洲/克雷斯顿"}                                      ,
{"America/Cuiaba","美洲/库艾巴"}                                         ,
{"America/Curacao","美洲/库拉索岛"}                                      ,
{"America/Danmarkshavn","美洲/丹麦港"}                                   ,
{"America/Dawson","美洲/道森"}                                           ,
{"America/Dawson_Creek","美洲/道森克里克"}                               ,
{"America/Denver","美洲/丹佛"}                                           ,
{"America/Detroit","美洲/底特律"}                                        ,
{"America/Dominica","美洲/多米尼克"}                                     ,
{"America/Edmonton","美洲/埃德蒙顿"}                                     ,
{"America/Eirunepe","美洲/埃鲁内佩"}                                     ,
{"America/El_Salvador","美洲/萨尔瓦多"}                                  ,
{"America/Ensenada","美洲/恩塞纳达"}                                     ,
{"America/Fort_Nelson","美洲/纳尔逊堡"}                                  ,
{"America/Fort_Wayne","美洲/韦恩堡"}                                     ,
{"America/Fortaleza","美洲/福塔莱萨"}                                    ,
{"America/Glace_Bay","America/Glace_Bay"}                                ,
{"America/Godthab","美洲/戈德哈布"}                                      ,
{"America/Goose_Bay","America/Goose_Bay"}                                ,
{"America/Grand_Turk","美洲/大特克岛"}                                   ,
{"America/Grenada","美洲/格林纳达"}                                      ,
{"America/Guadeloupe","美洲/瓜德罗普岛"}                                 ,
{"America/Guatemala","美洲/危地马拉"}                                    ,
{"America/Guayaquil","美洲/瓜亚基尔"}                                    ,
{"America/Guyana","美洲/圭亚那"}                                         ,
{"America/Halifax","美洲/哈利法克斯"}                                    ,
{"America/Havana","美洲/哈瓦那"}                                         ,
{"America/Hermosillo","美洲/埃莫西约"}                                   ,
{"America/Indiana/Indianapolis","美洲/印第安纳州/印第安纳波利斯"}        ,
{"America/Indiana/Knox","美洲/印第安纳州/诺克斯"}                        ,
{"America/Indiana/Marengo","美洲/印第安纳州/马伦戈"}                     ,
{"America/Indiana/Petersburg","美洲/印第安纳州/彼得斯堡"}                ,
{"America/Indiana/Tell_City","美洲/印第安纳州/Tell_City"}                ,
{"America/Indiana/Vevay","美洲/印第安纳州/沃韦"}                         ,
{"America/Indiana/Vincennes","美洲/印第安纳州/文森斯"}                   ,
{"America/Indiana/Winamac","美洲/印第安纳州/威纳马克"}                   ,
{"America/Indianapolis","美洲/印第安纳波利斯"}                           ,
{"America/Inuvik","美洲/伊努维克人"}                                     ,
{"America/Iqaluit","美洲/伊魁特"}                                        ,
{"America/Jamaica","美洲/牙买加"}                                        ,
{"America/Jujuy","美洲/胡胡伊省"}                                        ,
{"America/Juneau","美洲/朱诺"}                                           ,
{"America/Kentucky/Louisville","美洲/肯塔基州/路易斯维尔"}               ,
{"America/Kentucky/Monticello","美洲/肯塔基州/蒙蒂塞洛"}                 ,
{"America/Knox_IN","美洲/Knox_IN"}                                       ,
{"America/Kralendijk","美洲/克拉伦代克"}                                 ,
{"America/La_Paz","美洲/拉巴斯"}                                         ,
{"America/Lima","美洲/利马"}                                             ,
{"America/Los_Angeles","美洲/洛杉矶"}                                    ,
{"America/Louisville","美洲/路易斯维尔"}                                 ,
{"America/Lower_Princes","美洲/下王子郡"}                                ,
{"America/Maceio","美洲/马塞约"}                                         ,
{"America/Managua","美洲/马那瓜"}                                        ,
{"America/Manaus","美洲/马瑙斯"}                                         ,
{"America/Marigot","美洲/马里戈特"}                                      ,
{"America/Martinique","美洲/马提尼克岛"}                                 ,
{"America/Matamoros","美洲/马塔莫罗斯"}                                  ,
{"America/Mazatlan","美洲/马萨特兰"}                                     ,
{"America/Mendoza","美洲/门多萨"}                                        ,
{"America/Menominee","美国/梅诺米尼"}                                    ,
{"America/Merida","美洲/梅里达"}                                         ,
{"America/Metlakatla","美洲/梅特拉卡特拉"}                               ,
{"America/Mexico_City","美洲/墨西哥_城市"}                               ,
{"America/Miquelon","美洲/密克隆群岛"}                                   ,
{"America/Moncton","美洲/蒙克顿"}                                        ,
{"America/Monterrey","美洲/蒙特雷"}                                      ,
{"America/Montevideo","美洲/蒙得维的亚"}                                 ,
{"America/Montreal","美洲/蒙特利尔"}                                     ,
{"America/Montserrat","美洲/蒙特塞拉特"}                                 ,
{"America/Nassau","美洲/拿骚"}                                           ,
{"America/New_York","美洲/纽约"}                                         ,
{"America/Nipigon","美洲/尼皮贡"}                                        ,
{"America/Nome","美国/名称"}                                             ,
{"America/Noronha","美国/某地"}                                          ,
{"America/North_Dakota/Beulah","美洲/北达科他州/比尤拉"}                 ,
{"America/North_Dakota/Center","美洲/北达科他州/中部"}                   ,
{"America/North_Dakota/New_Salem","美洲/北达科他州/新塞勒姆"}            ,
{"America/Nuuk","美洲/努克"}                                             ,
{"America/Ojinaga","美洲/奥吉纳加"}                                      ,
{"America/Panama","美洲/巴拿马"}                                         ,
{"America/Pangnirtung","美洲/庞纳通"}                                    ,
{"America/Paramaribo","美洲/帕拉马里博"}                                 ,
{"America/Phoenix","美洲/菲尼克斯"}                                      ,
{"America/Port-au-Prince","美洲/太子港"}                                 ,
{"America/Port_of_Spain","美洲/西班牙港"}                                ,
{"America/Porto_Acre","美洲/阿卡港"}                                     ,
{"America/Porto_Velho","美洲/旧波尔图"}                                  ,
{"America/Puerto_Rico","美洲/波多黎各"}                                  ,
{"America/Punta_Arenas","美洲/蓬塔阿雷纳斯"}                             ,
{"America/Rainy_River","美洲/雷尼河"}                                    ,
{"America/Rankin_Inlet","兰金因莱特"}                                    ,
{"America/Recife","美洲/累西腓"}                                         ,
{"America/Regina","美洲/里贾纳"}                                         ,
{"America/Resolute","美国/坚决"}                                         ,
{"America/Rio_Branco","美洲/里奥布兰科"}                                 ,
{"America/Rosario","美洲/罗萨里奥"}                                      ,
{"America/Santa_Isabel","美洲/圣伊莎贝尔"}                               ,
{"America/Santarem","美洲/圣塔伦"}                                       ,
{"America/Santiago","美洲/圣地亚哥"}                                     ,
{"America/Santo_Domingo","美洲/圣多明各"}                                ,
{"America/Sao_Paulo","美洲/圣保罗"}                                      ,
{"America/Scoresbysund","美洲/斯科斯比松"}                               ,
{"America/Shiprock","美洲/希普洛克"}                                     ,
{"America/Sitka","美洲/锡特卡"}                                          ,
{"America/St_Barthelemy","美洲/圣巴泰勒米"}                              ,
{"America/St_Johns","美洲/圣约翰斯"}                                     ,
{"America/St_Kitts","美洲/圣基茨"}                                       ,
{"America/St_Lucia","美洲/圣卢西亚"}                                     ,
{"America/St_Thomas","美洲/圣托马斯"}                                    ,
{"America/St_Vincent","美洲/圣文森特"}                                   ,
{"America/Swift_Current","America/Swift_Current"}                        ,
{"America/Tegucigalpa","美洲/特古西加尔巴"}                              ,
{"America/Thule","美洲/图勒"}                                            ,
{"America/Thunder_Bay","美洲/雷湾"}                                      ,
{"America/Tijuana","美洲/蒂华纳"}                                        ,
{"America/Toronto","美洲/多伦多"}                                        ,
{"America/Tortola","美洲/托尔托拉岛"}                                    ,
{"America/Vancouver","美洲/温哥华"}                                      ,
{"America/Virgin","美国/维珍"}                                           ,
{"America/Whitehorse","美洲/怀特霍斯"}                                   ,
{"America/Winnipeg","美洲/温尼伯"}                                       ,
{"America/Yakutat","美洲/雅库塔特"}                                      ,
{"America/Yellowknife","美洲/黄刀镇"}                                    ,
{"Antarctica/Casey","南极洲/凯西"}                                       ,
{"Antarctica/Davis","南极洲/戴维斯"}                                     ,
{"Antarctica/DumontDUrville","南极洲/杜蒙杜维尔"}                        ,
{"Antarctica/Macquarie","南极洲/麦格理"}                                 ,
{"Antarctica/Mawson","南极洲/莫森"}                                      ,
{"Antarctica/McMurdo","南极洲/麦克默多"}                                 ,
{"Antarctica/Palmer","南极洲/帕尔默"}                                    ,
{"Antarctica/Rothera","南极洲/罗塞拉岛"}                                 ,
{"Antarctica/South_Pole","南极洲/南极点"}                                ,
{"Antarctica/Syowa","南极洲/昭和"}                                       ,
{"Antarctica/Troll","南极洲/巨魔"}                                       ,
{"Antarctica/Vostok","南极洲/东方"}                                      ,
{"Arctic/Longyearbyen","北极/朗伊尔城"}                                  ,
{"Asia/Aden","亚洲/亚丁"}                                                ,
{"Asia/Almaty","亚洲/阿拉木图"}                                          ,
{"Asia/Amman","亚洲/安曼"}                                               ,
{"Asia/Anadyr","亚洲/阿纳德尔"}                                          ,
{"Asia/Aqtau","亚洲/阿克套"}                                             ,
{"Asia/Aqtobe","亚洲/阿克托比"}                                          ,
{"Asia/Ashgabat","亚洲/阿什哈巴德"}                                      ,
{"Asia/Ashkhabad","亚洲/阿什哈巴德"}                                     ,
{"Asia/Atyrau","亚洲/阿特劳"}                                            ,
{"Asia/Baghdad","亚洲/巴格达"}                                           ,
{"Asia/Bahrain","亚洲/巴林"}                                             ,
{"Asia/Baku","亚洲/巴库"}                                                ,
{"Asia/Bangkok","亚洲/曼谷"}                                             ,
{"Asia/Barnaul","亚洲/巴尔瑙尔"}                                         ,
{"Asia/Beirut","亚洲/贝鲁特"}                                            ,
{"Asia/Bishkek","亚洲/比什凯克"}                                         ,
{"Asia/Brunei","亚洲/文莱"}                                              ,
{"Asia/Calcutta","亚洲/加尔各答"}                                        ,
{"Asia/Chita","亚洲/赤塔"}                                               ,
{"Asia/Choibalsan","亚洲/乔巴山"}                                        ,
{"Asia/Chongqing","亚洲/重庆"}                                           ,
{"Asia/Chungking","亚洲/重庆"}                                           ,
{"Asia/Colombo","亚洲/科伦坡"}                                           ,
{"Asia/Dacca","亚洲/达卡"}                                               ,
{"Asia/Damascus","亚洲/大马士革"}                                        ,
{"Asia/Dhaka","亚洲/达卡"}                                               ,
{"Asia/Dili","亚洲/帝力"}                                                ,
{"Asia/Dubai","亚洲/迪拜"}                                               ,
{"Asia/Dushanbe","亚洲/杜尚别"}                                          ,
{"Asia/Famagusta","亚洲/法马古斯塔"}                                     ,
{"Asia/Gaza","亚洲/加沙"}                                                ,
{"Asia/Harbin","亚洲/哈尔滨"}                                            ,
{"Asia/Hebron","亚洲/希伯伦"}                                            ,
{"Asia/Ho_Chi_Minh","亚洲/胡志明市"}                                     ,
{"Asia/Hong_Kong","亚洲/香港"}                                           ,
{"Asia/Hovd","亚洲/霍布德"}                                              ,
{"Asia/Irkutsk","亚洲/伊尔库茨克"}                                       ,
{"Asia/Istanbul","亚洲/伊斯坦布尔"}                                      ,
{"Asia/Jakarta","亚洲/雅加达"}                                           ,
{"Asia/Jayapura","亚洲/查亚普拉"}                                        ,
{"Asia/Jerusalem","亚洲/耶路撒冷"}                                       ,
{"Asia/Kabul","亚洲/喀布尔"}                                             ,
{"Asia/Kamchatka","亚洲/堪察加半岛"}                                     ,
{"Asia/Karachi","亚洲/卡拉奇"}                                           ,
{"Asia/Kashgar","亚洲/喀什"}                                             ,
{"Asia/Kathmandu","亚洲/加德满都"}                                       ,
{"Asia/Katmandu","亚洲/加德满都"}                                        ,
{"Asia/Khandyga","亚洲/坎迪加"}                                          ,
{"Asia/Kolkata","亚洲/加尔各答"}                                         ,
{"Asia/Krasnoyarsk","亚洲/克拉斯诺亚尔斯克"}                             ,
{"Asia/Kuala_Lumpur","亚洲/吉隆坡_吉隆坡"}                               ,
{"Asia/Kuching","亚洲/古晋"}                                             ,
{"Asia/Kuwait","亚洲/科威特"}                                            ,
{"Asia/Macao","亚洲/澳门"}                                               ,
{"Asia/Macau","亚洲/澳门"}                                               ,
{"Asia/Magadan","亚洲/马加丹"}                                           ,
{"Asia/Makassar","亚洲/望加锡"}                                          ,
{"Asia/Manila","亚洲/马尼拉"}                                            ,
{"Asia/Muscat","亚洲/马斯喀特"}                                          ,
{"Asia/Nicosia","亚洲/尼科西亚"}                                         ,
{"Asia/Novokuznetsk","亚洲/新库兹涅茨克"}                                ,
{"Asia/Novosibirsk","亚洲/新西伯利亚"}                                   ,
{"Asia/Omsk","亚洲/鄂木斯克"}                                            ,
{"Asia/Oral","亚洲/口头"}                                                ,
{"Asia/Phnom_Penh","亚洲/金边"}                                          ,
{"Asia/Pontianak","亚洲/笨珍"}                                           ,
{"Asia/Pyongyang","亚洲/平壤"}                                           ,
{"Asia/Qatar","亚洲/卡塔尔"}                                             ,
{"Asia/Qostanay","亚洲/库斯塔奈"}                                        ,
{"Asia/Qyzylorda","亚洲/克孜勒奥尔达"}                                   ,
{"Asia/Rangoon","亚洲/仰光"}                                             ,
{"Asia/Riyadh","亚洲/利雅得"}                                            ,
{"Asia/Saigon","亚洲/西贡"}                                              ,
{"Asia/Sakhalin","亚洲/萨哈林岛"}                                        ,
{"Asia/Samarkand","亚洲/撒马尔罕"}                                       ,
{"Asia/Seoul","亚洲/首尔"}                                               ,
{"Asia/Shanghai","亚洲/上海"}                                            ,
{"Asia/Singapore","亚洲/新加坡"}                                         ,
{"Asia/Srednekolymsk","亚洲/中科雷姆斯克"}                               ,
{"Asia/Taipei","亚洲/台北"}                                              ,
{"Asia/Tashkent","亚洲/塔什干"}                                          ,
{"Asia/Tbilisi","亚洲/第比利斯"}                                         ,
{"Asia/Tehran","亚洲/德黑兰"}                                            ,
{"Asia/Tel_Aviv","亚洲/特拉维夫"}                                        ,
{"Asia/Thimbu","亚洲/廷布"}                                              ,
{"Asia/Thimphu","亚洲/廷布"}                                             ,
{"Asia/Tokyo","亚洲/东京"}                                               ,
{"Asia/Tomsk","亚洲/托木斯克"}                                           ,
{"Asia/Ujung_Pandang","亚洲/乌戎_潘当"}                                  ,
{"Asia/Ulaanbaatar","亚洲/乌兰巴托"}                                     ,
{"Asia/Ulan_Bator","亚洲/乌兰巴托"}                                      ,
{"Asia/Urumqi","亚洲/乌鲁木齐"}                                          ,
{"Asia/Ust-Nera","亚洲/乌斯季-内拉"}                                     ,
{"Asia/Vientiane","亚洲/万象"}                                           ,
{"Asia/Vladivostok","亚洲/符拉迪沃斯托克"}                               ,
{"Asia/Yakutsk","亚洲/雅库茨克"}                                         ,
{"Asia/Yangon","亚洲/仰光"}                                              ,
{"Asia/Yekaterinburg","亚洲/叶卡捷琳堡"}                                 ,
{"Asia/Yerevan","亚洲/埃里温"}                                           ,
{"Atlantic/Azores","大西洋/亚速尔群岛"}                                  ,
{"Atlantic/Bermuda","大西洋/百慕大"}                                     ,
{"Atlantic/Canary","大西洋/加那利"}                                      ,
{"Atlantic/Cape_Verde","大西洋/佛得角"}                                  ,
{"Atlantic/Faeroe","大西洋/法罗"}                                        ,
{"Atlantic/Faroe","大西洋/法罗"}                                         ,
{"Atlantic/Jan_Mayen","大西洋/扬马延"}                                   ,
{"Atlantic/Madeira","大西洋/马德拉"}                                     ,
{"Atlantic/Reykjavik","大西洋/雷克雅未克"}                               ,
{"Atlantic/South_Georgia","大西洋/南乔治亚州"}                           ,
{"Atlantic/St_Helena","大西洋/圣赫勒拿岛"}                               ,
{"Atlantic/Stanley","大西洋/斯坦利"}                                     ,
{"Australia/ACT","澳大利亚/澳大利亚首都地区"}                            ,
{"Australia/Adelaide","澳大利亚/阿德莱德"}                               ,
{"Australia/Brisbane","澳大利亚/布里斯班"}                               ,
{"Australia/Broken_Hill","澳大利亚/布罗肯希尔"}                          ,
{"Australia/Canberra","澳大利亚/堪培拉"}                                 ,
{"Australia/Currie","澳大利亚/柯里"}                                     ,
{"Australia/Darwin","澳大利亚/达尔文"}                                   ,
{"Australia/Eucla","澳大利亚/尤克拉拉"}                                  ,
{"Australia/Hobart","澳大利亚/霍巴特"}                                   ,
{"Australia/LHI","澳大利亚/LHI"}                                         ,
{"Australia/Lindeman","澳大利亚/林德曼"}                                 ,
{"Australia/Lord_Howe","澳大利亚/豪勋爵"}                                ,
{"Australia/Melbourne","澳大利亚/墨尔本"}                                ,
{"Australia/NSW","澳大利亚/新南威尔士州"}                                ,
{"Australia/North","澳大利亚/北部"}                                      ,
{"Australia/Perth","澳大利亚/珀斯"}                                      ,
{"Australia/Queensland","澳大利亚/昆士兰"}                               ,
{"Australia/South","澳大利亚/南部"}                                      ,
{"Australia/Sydney","澳大利亚/悉尼"}                                     ,
{"Australia/Tasmania","澳大利亚/塔斯马尼亚"}                             ,
{"Australia/Victoria","澳大利亚/维多利亚"}                               ,
{"Australia/West","澳大利亚/西部"}                                       ,
{"Australia/Yancowinna","澳大利亚/扬科温纳"}                             ,
{"Brazil/Acre","巴西/阿卡"}                                              ,
{"Brazil/DeNoronha","巴西/迪诺罗尼亚群岛"}                               ,
{"Brazil/East","巴西/东部"}                                              ,
{"Brazil/West","巴西/西部"}                                              ,
{"CET","CET"}                                                            ,
{"CST6CDT","CST6CDT"}                                                    ,
{"Canada/Atlantic","加拿大/大西洋"}                                      ,
{"Canada/Central","加拿大/中部"}                                         ,
{"Canada/Eastern","加拿大/东部"}                                         ,
{"Canada/Mountain","加拿大/山区"}                                        ,
{"Canada/Newfoundland","加拿大/纽芬兰"}                                  ,
{"Canada/Pacific","加拿大/太平洋地区"}                                   ,
{"Canada/Saskatchewan","加拿大/萨斯喀彻温省"}                            ,
{"Canada/Yukon","加拿大/育空地区"}                                       ,
{"Chile/Continental","智利/大陆航空"}                                    ,
{"Chile/EasterIsland","智利/复活节岛"}                                   ,
{"Cuba","古巴"}                                                          ,
{"EET","欧洲东部时间"}                                                   ,
{"EST","东部时间"}                                                       ,
{"EST5EDT","EST5EDT"}                                                    ,
{"Egypt","埃及"}                                                         ,
{"Eire","爱尔兰"}                                                        ,
{"Etc/GMT","Etc/GMT"}                                                    ,
{"Etc/GMT+0","Etc/GMT+0"}                                                ,
{"Etc/GMT+1","Etc/GMT+1"}                                                ,
{"Etc/GMT+10","Etc/GMT+10"}                                              ,
{"Etc/GMT+11","Etc/GMT+11"}                                              ,
{"Etc/GMT+12","Etc/GMT+12"}                                              ,
{"Etc/GMT+2","Etc/GMT+2"}                                                ,
{"Etc/GMT+3","Etc/GMT+3"}                                                ,
{"Etc/GMT+4","Etc/GMT+4"}                                                ,
{"Etc/GMT+5","Etc/GMT+5"}                                                ,
{"Etc/GMT+6","Etc/GMT+6"}                                                ,
{"Etc/GMT+7","Etc/GMT+7"}                                                ,
{"Etc/GMT+8","Etc/GMT+8"}                                                ,
{"Etc/GMT+9","Etc/GMT+9"}                                                ,
{"Etc/GMT-0","Etc/GMT-0"}                                                ,
{"Etc/GMT-1","Etc/GMT-1"}                                                ,
{"Etc/GMT-10","Etc/GMT-10"}                                              ,
{"Etc/GMT-11","Etc/GMT-11"}                                              ,
{"Etc/GMT-12","Etc/GMT-12"}                                              ,
{"Etc/GMT-13","Etc/GMT-13"}                                              ,
{"Etc/GMT-14","Etc/GMT-14"}                                              ,
{"Etc/GMT-2","Etc/GMT-2"}                                                ,
{"Etc/GMT-3","Etc/GMT-3"}                                                ,
{"Etc/GMT-4","Etc/GMT-4"}                                                ,
{"Etc/GMT-5","Etc/GMT-5"}                                                ,
{"Etc/GMT-6","Etc/GMT-6"}                                                ,
{"Etc/GMT-7","Etc/GMT-7"}                                                ,
{"Etc/GMT-8","Etc/GMT-8"}                                                ,
{"Etc/GMT-9","Etc/GMT-9"}                                                ,
{"Etc/GMT0","Etc/GMT0"}                                                  ,
{"Etc/Greenwich","Etc/格林威治"}                                         ,
{"Etc/UCT","Etc/UCT"}                                                    ,
{"Etc/UTC","Etc/UTC"}                                                    ,
{"Etc/Universal","Etc/通用"}                                             ,
{"Etc/Zulu","Etc/祖鲁语"}                                                ,
{"Europe/Amsterdam","欧洲/阿姆斯特丹"}                                   ,
{"Europe/Andorra","欧洲/安道尔"}                                         ,
{"Europe/Astrakhan","欧洲/阿斯特拉罕"}                                   ,
{"Europe/Athens","欧洲/雅典"}                                            ,
{"Europe/Belfast","欧洲/贝尔法斯特"}                                     ,
{"Europe/Belgrade","欧洲/贝尔格莱德"}                                    ,
{"Europe/Berlin","欧洲/柏林"}                                            ,
{"Europe/Bratislava","欧洲/布拉迪斯拉发"}                                ,
{"Europe/Brussels","欧洲/布鲁塞尔"}                                      ,
{"Europe/Bucharest","欧洲/布加勒斯特"}                                   ,
{"Europe/Budapest","欧洲/布达佩斯"}                                      ,
{"Europe/Busingen","欧洲/布辛根"}                                        ,
{"Europe/Chisinau","欧洲/基希讷乌"}                                      ,
{"Europe/Copenhagen","欧洲/哥本哈根"}                                    ,
{"Europe/Dublin","欧洲/都柏林"}                                          ,
{"Europe/Gibraltar","欧洲/直布罗陀"}                                     ,
{"Europe/Guernsey","欧洲/根西岛"}                                        ,
{"Europe/Helsinki","欧洲/赫尔辛基"}                                      ,
{"Europe/Isle_of_Man","欧洲/马恩岛"}                                     ,
{"Europe/Istanbul","欧洲/伊斯坦布尔"}                                    ,
{"Europe/Jersey","欧洲/泽西岛"}                                          ,
{"Europe/Kaliningrad","欧洲/加里宁格勒"}                                 ,
{"Europe/Kiev","欧洲/基辅"}                                              ,
{"Europe/Kirov","欧洲/基洛夫"}                                           ,
{"Europe/Kyiv","欧洲/基辅"}                                              ,
{"Europe/Lisbon","欧洲/里斯本"}                                          ,
{"Europe/Ljubljana","欧洲/卢布尔雅那"}                                   ,
{"Europe/London","欧洲/伦敦"}                                            ,
{"Europe/Luxembourg","欧洲/卢森堡"}                                      ,
{"Europe/Madrid","欧洲/马德里"}                                          ,
{"Europe/Malta","欧洲/马耳他"}                                           ,
{"Europe/Mariehamn","欧洲/玛丽港"}                                       ,
{"Europe/Minsk","欧洲/明斯克"}                                           ,
{"Europe/Monaco","欧洲/摩纳哥"}                                          ,
{"Europe/Moscow","欧洲/莫斯科"}                                          ,
{"Europe/Nicosia","欧洲/尼科西亚"}                                       ,
{"Europe/Oslo","欧洲/奥斯陆"}                                            ,
{"Europe/Paris","欧洲/巴黎"}                                             ,
{"Europe/Podgorica","欧洲/波德戈里察"}                                   ,
{"Europe/Prague","欧洲/布拉格"}                                          ,
{"Europe/Riga","欧洲/里加"}                                              ,
{"Europe/Rome","欧洲/罗马"}                                              ,
{"Europe/Samara","欧洲/萨马拉"}                                          ,
{"Europe/San_Marino","欧洲/圣马力诺"}                                    ,
{"Europe/Sarajevo","欧洲/萨拉热窝"}                                      ,
{"Europe/Saratov","欧洲/萨拉托夫"}                                       ,
{"Europe/Simferopol","欧洲/辛菲罗波尔"}                                  ,
{"Europe/Skopje","欧洲/斯科普里"}                                        ,
{"Europe/Sofia","欧洲/索非亚"}                                           ,
{"Europe/Stockholm","欧洲/斯德哥尔摩"}                                   ,
{"Europe/Tallinn","欧洲/塔林"}                                           ,
{"Europe/Tirane","欧洲/地拉那"}                                          ,
{"Europe/Tiraspol","欧洲/蒂拉斯波尔"}                                    ,
{"Europe/Ulyanovsk","欧洲/乌里扬诺夫斯克"}                               ,
{"Europe/Uzhgorod","欧洲/乌日哥罗德"}                                    ,
{"Europe/Vaduz","欧洲/瓦杜兹"}                                           ,
{"Europe/Vatican","欧洲/梵蒂冈"}                                         ,
{"Europe/Vienna","欧洲/维也纳"}                                          ,
{"Europe/Vilnius","欧洲/维尔纽斯"}                                       ,
{"Europe/Volgograd","欧洲/伏尔加格勒"}                                   ,
{"Europe/Warsaw","欧洲/华沙"}                                            ,
{"Europe/Zagreb","欧洲/萨格勒布"}                                        ,
{"Europe/Zaporozhye","欧洲/扎波罗热"}                                    ,
{"Europe/Zurich","欧洲/苏黎世"}                                          ,
{"GB","国标"}                                                            ,
{"GB-Eire","英国-爱尔兰"}                                                ,
{"GMT","格林威治标准时间"}                                               ,
{"GMT+0","格林威治标准时间+0"}                                           ,
{"GMT-0","GMT-0"}                                                        ,
{"GMT0","格林威治标准时间0"}                                             ,
{"Greenwich","格林威治"}                                                 ,
{"HST","高温试验"}                                                       ,
{"Hongkong","香港"}                                                      ,
{"Iceland","冰岛"}                                                       ,
{"Indian/Antananarivo","印度/塔那那利佛"}                                ,
{"Indian/Chagos","印第安人/查戈斯人"}                                    ,
{"Indian/Christmas","印度/圣诞节"}                                       ,
{"Indian/Cocos","印度/科科斯"}                                           ,
{"Indian/Comoro","印度/科摩罗"}                                          ,
{"Indian/Kerguelen","印度/凯尔盖朗"}                                     ,
{"Indian/Mahe","印度/马埃岛"}                                            ,
{"Indian/Maldives","印度/马尔代夫"}                                      ,
{"Indian/Mauritius","印度/毛里求斯"}                                     ,
{"Indian/Mayotte","印度/马约特岛"}                                       ,
{"Indian/Reunion","印度/留尼汪岛"}                                       ,
{"Iran","伊朗"}                                                          ,
{"Israel","以色列"}                                                      ,
{"Jamaica","牙买加"}                                                     ,
{"Japan","日本"}                                                         ,
{"Kwajalein","夸贾林环礁"}                                               ,
{"Libya","利比亚"}                                                       ,
{"MET","梅特"}                                                           ,
{"MST","MST"}                                                            ,
{"MST7MDT","MST7MDT"}                                                    ,
{"Mexico/BajaNorte","墨西哥/北下巴"}                                     ,
{"Mexico/BajaSur","墨西哥/巴哈苏尔"}                                     ,
{"Mexico/General","墨西哥/一般"}                                         ,
{"NZ","新西兰"}                                                          ,
{"NZ-CHAT","新西兰聊天室"}                                               ,
{"Navajo","纳瓦霍语"}                                                    ,
{"PRC","中国"}                                                           ,
{"PST8PDT","PST8PDT"}                                                    ,
{"Pacific/Apia","太平洋/阿皮亚"}                                         ,
{"Pacific/Auckland","太平洋/奥克兰"}                                     ,
{"Pacific/Bougainville","太平洋/布干维尔岛"}                             ,
{"Pacific/Chatham","太平洋/查塔姆"}                                      ,
{"Pacific/Chuuk","太平洋/楚克"}                                          ,
{"Pacific/Easter","太平洋/复活节"}                                       ,
{"Pacific/Efate","太平洋/埃法特"}                                        ,
{"Pacific/Enderbury","太平洋/恩德伯里"}                                  ,
{"Pacific/Fakaofo","太平洋/法考福"}                                      ,
{"Pacific/Fiji","太平洋/斐济"}                                           ,
{"Pacific/Funafuti","太平洋/富纳富提"}                                   ,
{"Pacific/Galapagos","太平洋/加拉帕戈斯群岛"}                            ,
{"Pacific/Gambier","太平洋/甘比尔"}                                      ,
{"Pacific/Guadalcanal","太平洋/瓜达尔卡纳尔岛"}                          ,
{"Pacific/Guam","太平洋/关岛"}                                           ,
{"Pacific/Honolulu","太平洋/檀香山"}                                     ,
{"Pacific/Johnston","太平洋/约翰斯顿"}                                   ,
{"Pacific/Kanton","太平洋/关东"}                                         ,
{"Pacific/Kiritimati","太平洋/基里蒂马蒂"}                               ,
{"Pacific/Kosrae","太平洋/科斯雷"}                                       ,
{"Pacific/Kwajalein","太平洋/夸贾林环礁"}                                ,
{"Pacific/Majuro","太平洋/马朱罗"}                                       ,
{"Pacific/Marquesas","太平洋/马克萨斯"}                                  ,
{"Pacific/Midway","太平洋/中途岛"}                                       ,
{"Pacific/Nauru","太平洋/瑙鲁"}                                          ,
{"Pacific/Niue","太平洋/纽埃"}                                           ,
{"Pacific/Norfolk","太平洋/诺福克"}                                      ,
{"Pacific/Noumea","太平洋/努美阿"}                                       ,
{"Pacific/Pago_Pago","太平洋/帕果"}                                      ,
{"Pacific/Palau","太平洋/帕劳"}                                          ,
{"Pacific/Pitcairn","太平洋/皮特凯恩"}                                   ,
{"Pacific/Pohnpei","太平洋/波纳佩"}                                      ,
{"Pacific/Ponape","太平洋/波纳佩"}                                       ,
{"Pacific/Port_Moresby","太平洋/莫尔兹比港"}                             ,
{"Pacific/Rarotonga","太平洋/拉罗汤加"}                                  ,
{"Pacific/Saipan","太平洋/塞班岛"}                                       ,
{"Pacific/Samoa","太平洋/萨摩亚"}                                        ,
{"Pacific/Tahiti","太平洋/大溪地"}                                       ,
{"Pacific/Tarawa","太平洋/塔拉瓦"}                                       ,
{"Pacific/Tongatapu","太平洋/汤加塔布岛"}                                ,
{"Pacific/Truk","太平洋/特鲁克"}                                         ,
{"Pacific/Wake","太平洋/威克"}                                           ,
{"Pacific/Wallis","太平洋/沃利斯"}                                       ,
{"Pacific/Yap","太平洋/雅浦岛"}                                          ,
{"Poland","波兰"}                                                        ,
{"Portugal","葡萄牙"}                                                    ,
{"ROC","ROC"}                                                            ,
{"ROK","韩国"}                                                           ,
{"Singapore","新加坡"}                                                   ,
{"Turkey","Turkey"}                                                      ,
{"UCT","世界标准时间"}                                                   ,
{"US/Alaska","美国/阿拉斯加"}                                            ,
{"US/Aleutian","美国/阿留申群岛"}                                        ,
{"US/Arizona","美国/亚利桑那州"}                                         ,
{"US/Central","美国/中部"}                                               ,
{"US/East-Indiana","美国/东印第安纳州"}                                  ,
{"US/Eastern","美国/东部"}                                               ,
{"US/Hawaii","美国/夏威夷"}                                              ,
{"US/Indiana-Starke","美国/印第安纳州斯塔克"}                            ,
{"US/Michigan","美国/密歇根州"}                                          ,
{"US/Mountain","美国/山地"}                                              ,
{"US/Pacific","美国/太平洋地区"}                                         ,
{"US/Samoa","美国/萨摩亚"}                                               ,
{"UTC","世界标准时间"}                                                   ,
{"Universal","Universal"}                                                ,
{"W-SU","W-SU"}                                                          ,
{"WET","WET"}                                                            ,
{"Zulu","Zulu"}
    };

        /// <summary>
        /// 获取国家或城市当地时间
        /// </summary>
        /// <param name="country"></param>
        /// <param name="city"></param>
        /// <returns></returns>
        public static DateTime? GetCountryOrCityCurrentDateTime(string country, string city)
        {
            DateTime? currDt = null;

            var currTimezones = _timeZoneCityMappings.Where(x => x.Value.Contains(city)).ToList();
            if (!currTimezones.Any())
            {

                currTimezones = _timeZoneCityMappings.Where(x => x.Value.Contains(country)).ToList();


            }
            ;
            string timeZoneId = string.Empty;
            if (currTimezones.Count > 1)
            {
                currTimezones = currTimezones.Where(x => x.Value.Contains(country)).ToList();
                timeZoneId = currTimezones.FirstOrDefault().Key ?? string.Empty;
            }
            else timeZoneId = currTimezones.FirstOrDefault().Key ?? string.Empty;

            if (string.IsNullOrEmpty(timeZoneId)) return currDt;

            // Step 1: 获取当前 UTC 时间
            Instant now = SystemClock.Instance.GetCurrentInstant();

            // Step 2: 加载目标时区（以 "Asia/Shanghai" 为例）
            var timeZone = DateTimeZoneProviders.Tzdb[timeZoneId];

            if (timeZone == null) return currDt;

            // Step 3: 将 UTC 时间转换为目标时区的本地时间
            currDt = now.InZone(timeZone).ToDateTimeUnspecified();

            return currDt;
        }

        #endregion

        #region aspose

        /// <summary>
        /// aspose word 设置页脚
        /// </summary>
        /// <param name="builder"></param>
        /// <param name="footerLabel">页脚内容</param>
        /// <param name="font">字体名称</param>
        /// <param name="fontSize"><字体大小/param>
        public static void AsposeWordSetFooter(DocumentBuilder builder, string footerLabel, string font, int fontSize)
        {
            Section currentSection = builder.CurrentSection;
            var pageSetup = currentSection.PageSetup;
            pageSetup.DifferentFirstPageHeaderFooter = true;

            //第一页页脚
            builder.MoveToHeaderFooter(HeaderFooterType.FooterFirst);
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
            builder.Font.Name = font;
            builder.Font.Bold = true;
            builder.Font.Size = fontSize;
            builder.Write(footerLabel);

            //非第一页页脚
            builder.MoveToHeaderFooter(HeaderFooterType.FooterPrimary);
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
            builder.Font.Name = font;
            builder.Font.Bold = true;
            builder.Font.Size = fontSize;
            builder.Write(footerLabel);
        }

        #endregion

        #region 出入境费用

        #region 汇率

        /// <summary>
        /// 出入境费用 初始化汇率
        /// </summary>
        /// <returns></returns>
        public static async Task<List<CurrencyInfo>> EnterExitCostInitRate()
        {
            //默认币种显示
            List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>()
                {
                    new CurrencyInfo (){ CurrencyCode="USD",CurrencyName = "美元",Rate = 7.5000M },
                    new CurrencyInfo (){ CurrencyCode="EUR",CurrencyName = "欧元",Rate = 8.0000M },
                    new CurrencyInfo (){ CurrencyCode="GBP",CurrencyName = "英镑",Rate = 9.5000M },
                    new CurrencyInfo (){ CurrencyCode="JPY",CurrencyName = "日元",Rate = 0.0500M },
                    new CurrencyInfo (){ CurrencyCode="HKD",CurrencyName = "港币",Rate = 0.9500M },
                };

            var currencyRate = await _juHeApi.PostItemRateAsync(currencyInfos.Select(it => it.CurrencyCode!).ToArray());
            if (currencyRate.Count > 0)
            {
                foreach (var item in currencyInfos)
                {
                    var rateInfo = currencyRate.Where(it => it.Name.Equals(item.CurrencyName)).FirstOrDefault();
                    if (rateInfo != null)
                    {
                        decimal rate1 = Convert.ToDecimal(rateInfo.FSellPri) / 100.00M;

                        //if (rateInfo.Name.Equals("日元")) rate1 *= 1.3700M; //rate1 = rate1 * 1;
                        //else rate1 *= 1.035M;

                        rate1 *= 1.035M;

                        item.Rate = rate1.TruncDecimals(4) + 0.01M;
                    }
                }
            }
            return currencyInfos;
        }

        /// <summary>
        /// 出入境费用 实时汇率
        /// </summary>
        /// <returns></returns>
        public static async Task<List<dynamic>> EnterExitCostLiveRate()
        {
            List<dynamic> reteInfos = new List<dynamic>();
            //默认币种显示
            List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>()
                {
                    new CurrencyInfo (){ CurrencyCode="USD",CurrencyName = "美元",Rate = 7.5000M },
                    new CurrencyInfo (){ CurrencyCode="EUR",CurrencyName = "欧元",Rate = 8.0000M },
                    new CurrencyInfo (){ CurrencyCode="GBP",CurrencyName = "英镑",Rate = 9.5000M },
                    new CurrencyInfo (){ CurrencyCode="JPY",CurrencyName = "日元",Rate = 0.0500M },
                    new CurrencyInfo (){ CurrencyCode="HKD",CurrencyName = "港币",Rate = 0.9500M },

                };

            var currencyRate = await _juHeApi.PostItemRateAsync(currencyInfos.Select(it => it.CurrencyCode!).ToArray());
            if (currencyRate.Count > 0)
            {
                foreach (var item in currencyInfos)
                {
                    var rateInfo = currencyRate.Where(it => it.Name.Equals(item.CurrencyName)).FirstOrDefault();
                    if (rateInfo != null)
                    {
                        item.Rate = Convert.ToDecimal((Convert.ToDecimal(rateInfo.FSellPri == null ? 0.00M : rateInfo.FSellPri) / 100.00M).ToString("#0.0000"));

                        decimal rate1 = item.Rate * 1.0350M;
                        //rate1 += 0.01M;

                        reteInfos.Add(new
                        {
                            currCode = item.CurrencyCode,
                            currName = item.CurrencyName,
                            rate = rate1.TruncDecimals(4),
                            lastUpdateDt = rateInfo.Date + " " + rateInfo.Time
                        });
                    }
                }
            }
            return reteInfos;
        }

        /// <summary>
        /// 出入境费用 汇率验证
        /// </summary>
        /// <param name="groupId"></param>
        /// <param name="checkCurrencys">已使用币种</param>
        /// <returns></returns>
        public static async Task<(bool, string, List<CurrencyInfo>)> EnterExitCostCheckRate(int groupId, List<CurrencyInfo> checkCurrencys)
        {
            if (checkCurrencys == null || checkCurrencys.Count < 1) return (false, string.Empty, new List<CurrencyInfo>());

            var info = await _sqlSugar.Queryable<Grp_EnterExitCost>().FirstAsync(x => x.IsDel == 0 && x.DiId == groupId);
            if (info == null) return (false, string.Empty, new List<CurrencyInfo>());

            List<CurrencyInfo> liveRates;
            var cacheRateInfo = await _sqlSugar.Queryable<Sys_ExchangeRateRecord>()
                .OrderByDescending(x => x.CreateTime)
                .FirstAsync(x => x.IsDel == 0 && x.DiId == info.Id);
            if (cacheRateInfo == null)
            {
                var _currencyRate = await _juHeApi.PostItemRateAsync(Array.Empty<string>());
                if (_currencyRate.Count < 1) return (false, "获取汇率失败，请稍后再试！", new List<CurrencyInfo>());

                liveRates = _currencyRate.Select(it => new CurrencyInfo
                {
                    CurrencyCode = it.Name,
                    CurrencyName = it.Name,
                    Rate = Convert.ToDecimal((Convert.ToDecimal(it.FSellPri == null ? 0.00M : it.FSellPri) / 100.00M).ToString("#0.0000"))
                }).ToList();
            }
            else
            {
                liveRates = CommonFun.GetCurrencyChinaToList(cacheRateInfo.RateInfo);
                foreach (var x in liveRates)
                    x.Rate = (x.Rate / 100.00M).TruncDecimals(4);
            }

            var cacheRateInfos = CommonFun.GetCurrencyChinaToList(info.CurrencyRemark);

            var isSendMsg = false;
            var msgContent = new StringBuilder();

            //去掉费用未使用得币种
            if (checkCurrencys != null && checkCurrencys.Count > 0)
            {
                cacheRateInfos = cacheRateInfos.Where(x => checkCurrencys.Select(x1 => x1.CurrencyName).ToList().Contains(x.CurrencyName)).ToList();
            }

            if (cacheRateInfos.Any())
            {
                decimal rowthRate = 1.0350M;
                foreach (var cacheRate in cacheRateInfos)
                {
                    var liveRate = liveRates.Find(x => x.CurrencyName.Equals(cacheRate.CurrencyName));
                    if (liveRate == null) continue;

                    var currRate = (rowthRate * liveRate.Rate).TruncDecimals(4);
                    if (cacheRate.Rate != currRate)
                    {
                        isSendMsg = true;
                        //>团组归属：<font color='info'>{groupName}</font>
                        msgContent.AppendLine($">- {cacheRate.CurrencyName}汇率（页面保存）：{cacheRate.Rate:#0.0000}  / 实时汇率（接口保存，已上浮1.0350）：{currRate:#0.0000}【原接口输出汇率为：{liveRate.Rate:#0.0000}】");
                    }
                }
                return (isSendMsg, msgContent.ToString(), liveRates);
            }

            return (false, string.Empty, new List<CurrencyInfo>());
        }

        #endregion

        #region 三公费用明细 文件下载

        /// <summary>
        /// 获取三公费用标准city
        /// </summary>
        /// <param name="placeData"></param>
        /// <param name="nationalTravelFeeId"></param>
        /// <returns></returns>
        private static string GetEnterExitCostExportCity(List<Grp_NationalTravelFee> placeData, int nationalTravelFeeId)
        {
            string _city = string.Empty;

            if (placeData.Count < 1) return _city;

            var data = placeData.Find(it => it.Id == nationalTravelFeeId);
            if (data == null) return _city;

            string country = data.Country;
            string city = data.City;
            if (city.Contains("其他城市") || city.Contains("所有城市")) _city = $"{country}-{city}";
            else _city = city;
            return _city;
        }

        /// <summary>
        /// 三公费用明细 文件下载
        /// </summary>
        /// <param name="dto"></param>
        /// <param name="fileFormat"></param>
        /// <returns></returns>
        public static async Task<JsonView> EnterExitCostDownload(EnterExitCostDownloadDto dto, string fileFormat = "pdf")
        {
            var _view = new JsonView() { Code = StatusCodes.Status204NoContent };

            if (dto.DiId < 1)
            {
                _view.Msg = "请传入有效的DiId参数；";
                return _view;
            }

            if (dto.ExportType < 1)
            {
                _view.Msg = MsgTips.Port;
                return _view;
            }

            if (dto.SubTypeId < 1)
            {
                _view.Msg = @"请传入有效的SubTypeId参数； 
                            1 明细表 --> 1005(默认明细表) 1006(因公出国（境）经费测算明细表) 1007(四川省商务厅出国经费财政先行审核表)  
                            2 表格 --> 1008(派员单位出(境)任务和预算审批意见表) 1009(省级单位出(境)经费报销单)
                            3 团组成员名单 1 团组成员名单";
                return _view;
            }

            try
            {
                var enterExitCosts = _sqlSugar.Queryable<Grp_EnterExitCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
                var dayAndCosts = _sqlSugar.Queryable<Grp_DayAndCost>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).ToList();
                if (enterExitCosts == null)
                {
                    _view.Msg = @"该团组未填写出入境费用；";
                    return _view;
                }

                //数据源
                List<Grp_DayAndCost> dac1 = dayAndCosts.Where(it => it.Type == 1).ToList(); //住宿费
                List<Grp_DayAndCost> dac2 = dayAndCosts.Where(it => it.Type == 2).ToList(); //伙食费
                List<Grp_DayAndCost> dac3 = dayAndCosts.Where(it => it.Type == 3).ToList(); //公杂费
                List<Grp_DayAndCost> dac4 = dayAndCosts.Where(it => it.Type == 4).ToList(); //培训费

                var _CurrDatas = _sqlSugar.Queryable<Sys_SetData>().Where(it => it.IsDel == 0 && it.STid == 66).ToList();
                var _DelegationInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().Where(it => it.IsDel == 0 && it.Id == dto.DiId).First();
                var DeleClientList = _sqlSugar.Queryable<Grp_TourClientList>()
                                                      .LeftJoin<Crm_DeleClient>((tcl, dc) => tcl.ClientId == dc.Id && dc.IsDel == 0)
                                                      .LeftJoin<Crm_CustomerCompany>((tcl, dc, cc) => dc.CrmCompanyId == cc.Id && dc.IsDel == 0)
                                                      .Where((tcl, dc, cc) => tcl.IsDel == 0 && tcl.DiId == dto.DiId)
                                                      .Select((tcl, dc, cc) => new
                                                      {
                                                          Name = dc.LastName + dc.FirstName,
                                                          Sex = dc.Sex,
                                                          Birthday = dc.BirthDay,
                                                          Company = cc.CompanyFullName,
                                                          Job = dc.Job
                                                      })
                                                      .ToList();
                var blackCode = _sqlSugar.Queryable<Air_TicketBlackCode>().Where(it => it.IsDel == 0 && it.DiId == dto.DiId).First();
                var threeCodes = _sqlSugar.Queryable<Res_ThreeCode>().Where(it => it.IsDel == 0).ToList();
                var placeData = _sqlSugar.Queryable<Grp_NationalTravelFee>().Where(it => it.IsDel == 0).ToList();
                var rateDatas = await enterExitCosts.CurrencyRemark.SplitExchangeRate();


                _DelegationInfo.VisitCountry = _DelegationInfo.VisitCountry.Replace("|", "、");

                if (dto.ExportType == 1) //明细表
                {
                    if (dto.SubTypeId == 1005) //1005(默认明细表)
                    {

                        if (!fileFormat.Equals("pdf")) fileFormat = "docx";

                        //获取模板
                        string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/出入境费用表模板.docx");

                        //载入模板
                        Document doc = new Document(tempPath);

                        DocumentBuilder builder = new DocumentBuilder(doc);

                        //利用键值对存放数据
                        Dictionary<string, string> dic = new Dictionary<string, string>();

                        decimal stayFeeTotal = dayAndCosts.Where(it => it.Type == 1).Sum(it => it.SubTotal);  // 住宿费
                        decimal mealsFeeTotal = dayAndCosts.Where(it => it.Type == 2).Sum(it => it.SubTotal);  // 伙食费费
                        decimal miscellaneousFeeTotal = dayAndCosts.Where(it => it.Type == 3).Sum(it => it.SubTotal);  // 公杂费
                        decimal tainFeeTotal = dayAndCosts.Where(it => it.Type == 4).Sum(it => it.SubTotal);  // 培训费

                        decimal insidePayTotal = enterExitCosts.InsidePay;

                        string row1_1 = "";
                        if (enterExitCosts.Visa > 0)
                        {
                            //insidePayTotal += enterExitCosts.Visa;
                            row1_1 = $"签证费： {enterExitCosts.Visa.ToString("#0.00")} 人民币/人";
                            //if (!string.IsNullOrEmpty(enterExitCosts.VisaRemark))
                            //{
                            //    row1_1 += $"\t签证费用描述： {enterExitCosts.VisaRemark}";
                            //}
                        }

                        string row1_2 = "";
                        if (enterExitCosts.YiMiao > 0)
                        {
                            //insidePayTotal += enterExitCosts.YiMiao;
                            row1_2 += $"疫苗费：{enterExitCosts.YiMiao.ToString("#0.00")} 人民币/人";
                        }
                        if (enterExitCosts.HeSuan > 0)
                        {
                            //insidePayTotal += enterExitCosts.HeSuan;
                            row1_2 += $"核酸检测费：{enterExitCosts.HeSuan.ToString("#0.00")} 人民币/人";
                        }
                        if (enterExitCosts.Service > 0)
                        {
                            //insidePayTotal += enterExitCosts.Service;
                            row1_2 += $"服务费：{enterExitCosts.Service.ToString("#0.00")} 人民币/人";
                        }

                        string row1_3 = "";
                        if (enterExitCosts.Safe > 0)
                        {
                            //insidePayTotal += enterExitCosts.Safe;
                            row1_3 += $"保险费：{enterExitCosts.Safe.ToString("#0.00")} 人民币/人";
                        }
                        if (enterExitCosts.Ticket > 0)
                        {
                            //insidePayTotal += enterExitCosts.Ticket;
                            row1_3 += $"参展门票：{enterExitCosts.Ticket.ToString("#0.00")} 人民币/人";
                        }
                        string row1 = "";
                        if (!string.IsNullOrEmpty(row1_1)) row1 += $"{row1_1}\r\n";
                        if (!string.IsNullOrEmpty(row1_2)) row1 += $"{row1_2}\r\n";
                        if (!string.IsNullOrEmpty(row1_3)) row1 += $"{row1_3}";

                        dic.Add("InsidePay", insidePayTotal.ToString("#0.00"));
                        dic.Add("Row1Str", row1);

                        dic.Add("OutsideJJ", enterExitCosts.OutsideJJPay.ToString("#0.00"));
                        dic.Add("OutsaideGW", enterExitCosts.OutsideGWPay.ToString("#0.00"));
                        dic.Add("AirJJ", enterExitCosts.AirJJ.ToString("#0.00"));
                        dic.Add("AirGW", enterExitCosts.AirGW.ToString("#0.00"));
                        dic.Add("CityTranffic", enterExitCosts.CityTranffic.ToString("#0.00"));
                        dic.Add("SubZS", stayFeeTotal.ToString("#0.00"));
                        dic.Add("SubHS", mealsFeeTotal.ToString("#0.00"));

                        string miscellaneousFeeTotalStr = miscellaneousFeeTotal.ToString("#0.00");
                        dic.Add("SubGZF", miscellaneousFeeTotalStr);
                        //dic.Add("SubPX", tainFeeTotal.ToString("#0.00"));

                        decimal subJJC = insidePayTotal + stayFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + tainFeeTotal + enterExitCosts.OutsideJJPay;
                        decimal subGWC = insidePayTotal + stayFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + tainFeeTotal + enterExitCosts.OutsideGWPay;
                        dic.Add("SubJJC", subJJC.ToString("#0.00"));
                        dic.Add("SubGWC", subGWC.ToString("#0.00"));

                        #region 填充word模板书签内容
                        foreach (var key in dic.Keys)
                        {
                            builder.MoveToBookmark(key);
                            builder.Write(dic[key]);
                        }

                        #endregion

                        #region 填充word表格内容

                        ////获读取指定表格方法二
                        NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
                        Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
                        for (int i = 0; i < dac1.Count; i++)
                        {

                            Grp_DayAndCost dac = dac1[i];
                            if (dac == null) continue;

                            builder.MoveToCell(0, i, 0, 0);
                            builder.Write("第" + dac.Days.ToString() + "晚：");

                            builder.MoveToCell(0, i, 1, 0);
                            //builder.Write(placeData.Find(it => it.Id == dac.NationalTravelFeeId)?.Country ?? "Unknown");
                            //builder.Write(dac.Place == null ? "" : dac.Place);
                            builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));


                            builder.MoveToCell(0, i, 2, 0);
                            builder.Write("费用标准：");

                            string curr = "";
                            var currData = _CurrDatas.Where(it => it.Id == dac.Currency).FirstOrDefault();
                            if (currData != null)
                            {
                                curr = currData.Name;
                            }
                            builder.MoveToCell(0, i, 3, 0);
                            builder.Write(dac.Cost.ToString("#0.00") + curr);

                            builder.MoveToCell(0, i, 4, 0);
                            builder.Write("费用小计：");

                            builder.MoveToCell(0, i, 5, 0);
                            builder.Write(dac.SubTotal.ToString("#0.00") + "CNY");
                        }

                        //删除多余行
                        while (table1.Rows.Count > dac1.Count)
                        {
                            table1.Rows.RemoveAt(dac1.Count);
                        }

                        Aspose.Words.Tables.Table table2 = allTables[1] as Aspose.Words.Tables.Table;
                        for (int i = 0; i < dac2.Count; i++)
                        {

                            Grp_DayAndCost dac = dac2[i];
                            if (dac == null) continue;

                            builder.MoveToCell(1, i, 0, 0);
                            builder.Write("第" + dac.Days.ToString() + "天：");

                            builder.MoveToCell(1, i, 1, 0);
                            builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));

                            builder.MoveToCell(1, i, 2, 0);
                            builder.Write("费用标准：");

                            string curr = "";
                            var currData = _CurrDatas.Where(it => it.Id == dac.Currency).FirstOrDefault();
                            if (currData != null)
                            {
                                curr = currData.Name;
                            }
                            builder.MoveToCell(1, i, 3, 0);
                            builder.Write(dac.Cost.ToString("#0.00") + curr);

                            builder.MoveToCell(1, i, 4, 0);
                            builder.Write("费用小计：");

                            builder.MoveToCell(1, i, 5, 0);
                            builder.Write(dac.SubTotal.ToString("#0.00") + "CNY");

                        }

                        //删除多余行
                        while (table2.Rows.Count > dac2.Count)
                        {
                            table2.Rows.RemoveAt(dac2.Count);
                        }

                        Aspose.Words.Tables.Table table3 = allTables[2] as Aspose.Words.Tables.Table;
                        for (int i = 0; i < dac3.Count; i++)
                        {

                            Grp_DayAndCost dac = dac3[i];
                            if (dac == null) continue;

                            builder.MoveToCell(2, i, 0, 0);
                            builder.Write("第" + dac.Days.ToString() + "天：");

                            builder.MoveToCell(2, i, 1, 0);

                            builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));

                            builder.MoveToCell(2, i, 2, 0);
                            builder.Write("费用标准：");

                            string curr = "";
                            var currData = _CurrDatas.Where(it => it.Id == dac.Currency).FirstOrDefault();
                            if (currData != null)
                            {
                                curr = currData.Name;
                            }
                            builder.MoveToCell(2, i, 3, 0);
                            builder.Write(dac.Cost.ToString("#0.00") + curr);

                            builder.MoveToCell(2, i, 4, 0);
                            builder.Write("费用小计：");

                            builder.MoveToCell(2, i, 5, 0);
                            builder.Write(dac.SubTotal.ToString("#0.00") + "CNY");

                        }

                        //删除多余行
                        while (table3.Rows.Count > dac3.Count)
                        {
                            table3.Rows.RemoveAt(dac3.Count);
                        }

                        #endregion

                        //文件名
                        //string strFileName = $"{_DelegationInfo.TeamName}出入境费用{Guid.NewGuid().ToString()}.{fileFormat}";
                        //string strFileName = $"{_DelegationInfo.TeamName}-出入境费用{Guid.NewGuid().ToString()}.{fileFormat}";
                        string strFileName = $"{_DelegationInfo.TeamName}{DateTime.UtcNow.ToString("yyyyMMddHHmmss")}.{fileFormat}";

                        AsposeHelper.removewatermark_v2180();
                        //doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
                        var saveFormat = Aspose.Words.SaveFormat.Docx;

                        if (fileFormat.Equals("pdf"))
                        {
                            //doc.LayoutOptions.IsShowComments = false;
                            doc.LayoutOptions.IsShowHiddenText = false;
                            doc.LayoutOptions.IsShowParagraphMarks = false;
                            doc.LayoutOptions.RevisionOptions.DeletedTextEffect = RevisionTextEffect.None;
                            doc.AcceptAllRevisions();

                            Aspose.Words.Saving.PdfSaveOptions options = new Aspose.Words.Saving.PdfSaveOptions();
                            options.Compliance = PdfCompliance.PdfA1a;
                            options.CreateNoteHyperlinks = true;

                            doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName, options);
                            //_view.Data = new { Url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName };
                            _view.Data = new { Url = strFileName };
                            _view.Msg = "成功";
                            return _view;
                        }

                        doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName, saveFormat);
                        _view.Data = new { Url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName };
                        _view.Msg = "成功";
                        return _view;

                    }
                    else if (dto.SubTypeId == 1006)//1006(因公出国（境）经费测算明细表)
                    {
                        //获取模板
                        string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/因公出国（境）经费测算明细表.docx");
                        //载入模板
                        Document doc = new Document(tempPath);

                        DocumentBuilder builder = new DocumentBuilder(doc);

                        Dictionary<string, string> dic = new Dictionary<string, string>();

                        if (blackCode != null && !string.IsNullOrWhiteSpace(blackCode.BlackCode))
                        {
                            List<string> list = new List<string>();
                            try
                            {
                                var spilitArr = Regex.Split(blackCode.BlackCode, "\r\n");
                                foreach (var item in spilitArr)
                                {
                                    var spDotandEmpty = item.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
                                    var depCode = spDotandEmpty[2].Substring(0, 3);
                                    var arrCode = spDotandEmpty[2].Substring(3, 3);
                                    string depName = threeCodes.Find(it => it.Three.Equals(depCode)).City,
                                           arrName = threeCodes.Find(it => it.Three.Equals(arrCode)).City;

                                    list.Add(depName);
                                    list.Add(arrName);
                                }

                                list = list.Distinct().ToList();
                                dic.Add("ReturnCode", string.Join("-", list).TrimEnd('-'));
                            }
                            catch (Exception)
                            {
                                dic.Add("ReturnCode", "行程录入不正确!");
                            }
                        }
                        else
                        {
                            dic.Add("ReturnCode", "未录入行程!");
                        }

                        dic.Add("ReturnCodeAir", dic["ReturnCode"]);
                        dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
                        dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));

                        if (dic.ContainsKey("VisitStartDate") && dic.ContainsKey("VisitEndDate"))
                        {
                            TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
                            dic.Add("Day", sp.Days.ToString());
                        }
                        dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
                        dic.Add("ClientUnit", _DelegationInfo.ClientUnit);

                        //var Names = string.Join("、", DeleClientList.Select(it => it.Name).ToList()).TrimEnd('、');
                        //dic.Add("Names", Names);


                        NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
                        Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;

                        decimal dac1totalPrice = 0.00M;
                        int accommodationStartIndex = 6, foodandotherStartIndex = 22;
                        foreach (var dac in dac1)
                        {
                            if (dac.SubTotal == 0.00M)
                            {
                                continue;
                            }

                            //builder.MoveToCell(0, accommodationStartIndex, 0, 0);
                            //builder.Write(DeleClientList[i].LastName + DeleClientList[i].Name); 
                            builder.MoveToCell(0, accommodationStartIndex, 1, 0);

                            builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
                            builder.MoveToCell(0, accommodationStartIndex, 2, 0);

                            string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknown";
                            builder.Write(currency);//币种
                            builder.MoveToCell(0, accommodationStartIndex, 3, 0);
                            builder.Write(dac.Cost.ToString("#0.00"));//标准
                            builder.MoveToCell(0, accommodationStartIndex, 4, 0);
                            builder.Write("");//人数
                            builder.MoveToCell(0, accommodationStartIndex, 5, 0);
                            builder.Write("");//天数
                            builder.MoveToCell(0, accommodationStartIndex, 6, 0);
                            builder.Write(dac.SubTotal.ToString("#0.00"));//小计
                            builder.MoveToCell(0, accommodationStartIndex, 7, 0);

                            decimal rate = 0.00M;
                            rate = rateDatas.Find(it => it.CurrencyName.Equals(currency))?.Rate ?? 0.00M;

                            builder.Write(rate.ToString("#0.0000"));//汇率
                            builder.MoveToCell(0, accommodationStartIndex, 8, 0);

                            decimal rbmPrice = dac.SubTotal;
                            builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
                            accommodationStartIndex++;
                            dac1totalPrice += Convert.ToDecimal(rbmPrice.ToString("#0.00"));
                        }

                        dic.Add("dac1totalPrice", dac1totalPrice.ToString("#0.00"));

                        for (int i = 21; i > (dac1.Count == 0 ? 1 : dac1.Count) + 6; i--)
                        {
                            table1.Rows.RemoveAt(i - 1);
                            foodandotherStartIndex--;
                        }

                        if (dac2.Count == dac3.Count)//国家 币种 金额
                        {
                            for (int i = 0; i < dac2.Count; i++)
                            {
                                dac2[i].SubTotal = dac2[i].SubTotal + dac3[i].SubTotal; //小计
                                dac2[i].Cost = dac3[i].Cost + dac2[i].Cost; //标准
                            }
                        }

                        decimal dac2totalPrice = 0.00M;

                        foreach (var dac in dac2)
                        {
                            if (dac.SubTotal == 0)
                            {
                                continue;
                            }

                            builder.MoveToCell(0, foodandotherStartIndex, 1, 0);

                            builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));
                            builder.MoveToCell(0, foodandotherStartIndex, 2, 0);
                            string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknown";
                            builder.Write(currency);//币种
                            builder.MoveToCell(0, foodandotherStartIndex, 3, 0);
                            builder.Write(dac.Cost.ToString("#0.00"));//标准
                            builder.MoveToCell(0, foodandotherStartIndex, 4, 0);
                            builder.Write("");//人数
                            builder.MoveToCell(0, foodandotherStartIndex, 5, 0);
                            builder.Write("");//天数
                            builder.MoveToCell(0, foodandotherStartIndex, 6, 0);
                            builder.Write(dac.SubTotal.ToString("#0.00"));//小计
                            builder.MoveToCell(0, foodandotherStartIndex, 7, 0);

                            decimal rate = 0.00M;
                            rate = rateDatas.Find(it => it.CurrencyName.Equals(currency))?.Rate ?? 0.00M;

                            builder.Write(rate.ToString("#0.0000"));//汇率

                            builder.MoveToCell(0, foodandotherStartIndex, 8, 0);
                            decimal rbmPrice = dac.SubTotal;
                            builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
                            foodandotherStartIndex++;
                            dac2totalPrice += Convert.ToDecimal(rbmPrice.ToString("#0.00"));
                        }

                        dic.Add("dac2totalPrice", dac2totalPrice.ToString("#0.00"));

                        for (int i = foodandotherStartIndex + (15 - dac2.Count); i > (dac2.Count == 0 ? 1 : 0) + foodandotherStartIndex; i--)
                        {
                            table1.Rows.RemoveAt(i - 1);
                        }


                        dic.Add("CityTranffic", enterExitCosts.CityTranffic.ToString("#0.00"));

                        string otherFeeStr = "";
                        if (enterExitCosts.Visa > 0) otherFeeStr += $"签证费： {enterExitCosts.Visa.ToString("#0.00")} 元，";
                        if (enterExitCosts.Safe > 0) otherFeeStr += $"保险费： {enterExitCosts.Safe.ToString("#0.00")} 元，";
                        if (enterExitCosts.Ticket > 0) otherFeeStr += $"参展门票费： {enterExitCosts.Ticket.ToString("#0.00")} 元，";

                        if (otherFeeStr.Length > 0)
                        {
                            otherFeeStr = otherFeeStr.Substring(0, otherFeeStr.Length - 1);
                            otherFeeStr = $"({otherFeeStr})";

                            dic.Add("OtherFeeStr", otherFeeStr);
                        }

                        //总计
                        decimal allPrice = dac1totalPrice + dac2totalPrice + enterExitCosts.Visa + enterExitCosts.Safe + enterExitCosts.Ticket;

                        //国际旅费
                        string outsideJJ = "";
                        string allPriceJJ = "";
                        if (enterExitCosts.SumJJC == 1)
                        {
                            outsideJJ = string.Format(@"经济舱:{0} 元/人", enterExitCosts.AirJJ.ToString("#0.00"));
                            allPriceJJ = string.Format(@"经济舱:{0} 元/人", (allPrice + enterExitCosts.OutsideJJPay).ToString("#0.00"));
                        }
                        string outsideGW = "";
                        string allPriceGW = "";
                        if (enterExitCosts.SumGWC == 1)
                        {
                            outsideGW = string.Format(@"公务舱:{0} 元/人", enterExitCosts.AirGW.ToString("#0.00"));
                            allPriceGW = string.Format(@"公务舱:{0} 元/人", (allPrice + enterExitCosts.OutsideGWPay).ToString("#0.00"));
                        }

                        if (enterExitCosts.SumJJC == 1 || enterExitCosts.SumGWC == 1)
                        {
                            string InTravelPriceStr = string.Format(@" ({0} {1})", outsideJJ, outsideGW);
                            dic.Add("InTravelPrice", InTravelPriceStr);

                            string FinalSumPriceStr = string.Format(@" ({0} {1})", allPriceJJ, allPriceGW);
                            dic.Add("FinalSumPrice", FinalSumPriceStr);
                        }

                        //dic.Add("VisaPay", enterExitCosts.Visa.ToString("#0.00"));
                        //dic.Add("SafePay", enterExitCosts.Safe.ToString("#0.00"));
                        //dic.Add("YiMiao", enterExitCosts.YiMiao.ToString("#0.00"));

                        foreach (var key in dic.Keys)
                        {
                            builder.MoveToBookmark(key);
                            builder.Write(dic[key]);
                        }

                        //模板文件名
                        string strFileName = $"{_DelegationInfo.TeamName}因公出国（境）经费测算明细表.docx";
                        doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
                        string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;

                        _view.Data = new { Url = url };
                        _view.Msg = "成功";
                        return _view;

                    }
                    else if (dto.SubTypeId == 1007) //1007(四川省商务厅出国经费财政先行审核表)
                    {
                        //获取模板
                        string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/四川省商务厅出国经费财政先行审核表.xls");
                        //载入模板
                        WorkbookDesigner designer = new WorkbookDesigner();
                        designer.Workbook = new Workbook(tempPath);

                        Dictionary<string, string> dic = new Dictionary<string, string>();

                        if (blackCode != null && !string.IsNullOrWhiteSpace(blackCode.BlackCode))
                        {
                            List<string> list = new List<string>();
                            try
                            {
                                var spilitArr = Regex.Split(blackCode.BlackCode, "\r\n");
                                foreach (var item in spilitArr)
                                {
                                    var spDotandEmpty = item.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
                                    var depCode = spDotandEmpty[2].Substring(0, 3);
                                    var arrCode = spDotandEmpty[2].Substring(3, 3);
                                    string depName = threeCodes.Find(it => it.Three.Equals(depCode))?.City ?? "Unknwon",
                                           arrName = threeCodes.Find(it => it.Three.Equals(arrCode))?.City ?? "Unknown";

                                    list.Add(depName);
                                    list.Add(arrName);
                                }

                                list = list.Distinct().ToList();
                                dic.Add("ReturnCode", string.Join("-", list).TrimEnd('-'));
                            }
                            catch (Exception)
                            {
                                dic.Add("ReturnCode", "行程录入不正确!");
                            }
                        }
                        else
                        {
                            dic.Add("ReturnCode", "未录入行程!");
                        }

                        dic.Add("ReturnCodeAir", dic["ReturnCode"]);
                        dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
                        dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));

                        if (dic.ContainsKey("VisitStartDate") && dic.ContainsKey("VisitEndDate"))
                        {
                            TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
                            dic.Add("Day", sp.Days.ToString());
                        }
                        dic.Add("VisitCountry", _DelegationInfo.VisitCountry);
                        dic.Add("ClientUnit", _DelegationInfo.ClientUnit);

                        var Names = string.Join("、", DeleClientList.Select(it => it.Name).ToList()).TrimEnd('、');

                        designer.SetDataSource("ClientUnit", _DelegationInfo.ClientUnit);
                        designer.SetDataSource("VisitCountry", _DelegationInfo.VisitCountry);
                        designer.SetDataSource("Group", _DelegationInfo.TeamName);
                        designer.SetDataSource("ClientUnitTitle", _DelegationInfo.TeamName);
                        designer.SetDataSource("Name", Names);
                        designer.SetDataSource("VisitStartDate", dic["VisitStartDate"] + "-" + dic["VisitEndDate"]);
                        designer.SetDataSource("Day", dic["Day"] + "天");
                        designer.SetDataSource("ReturnCode", dic["ReturnCode"]);
                        designer.SetDataSource("ReturnCodeAir", dic["ReturnCodeAir"]);

                        int startIndex = 10;
                        const int startIndexcopy = 10;

                        if (dac2.Count == dac3.Count)//国家 币种 金额
                        {
                            for (int i = 0; i < dac2.Count; i++)
                            {
                                dac2[i].SubTotal = dac2[i].SubTotal + dac3[i].SubTotal; //小计
                                dac2[i].Cost = dac3[i].Cost + dac2[i].Cost; //标准
                            }
                        }
                        DataTable dtdac1 = new DataTable();
                        List<string> place = new List<string>();
                        dtdac1.Columns.AddRange(new DataColumn[] {
                            new DataColumn(){ ColumnName = "city"},
                            new DataColumn(){ ColumnName = "curr"},
                            new DataColumn(){ ColumnName = "criterion"},
                            new DataColumn(){ ColumnName = "number",DataType = typeof(int)},
                            new DataColumn(){ ColumnName = "day",DataType = typeof(int)},
                            new DataColumn(){ ColumnName = "cost",  DataType = typeof(decimal)},
                            new DataColumn(){ ColumnName = "rate",  DataType = typeof(decimal) },
                            new DataColumn(){ ColumnName = "costRMB",  DataType = typeof(decimal) },
                        });
                        DataTable dtdac2 = new DataTable();
                        dtdac2.Columns.AddRange(new DataColumn[] {
                            new DataColumn(){ ColumnName = "city"},
                            new DataColumn(){ ColumnName = "curr"},
                            new DataColumn(){ ColumnName = "criterion"},
                            new DataColumn(){ ColumnName = "number",DataType = typeof(int)},
                            new DataColumn(){ ColumnName = "day",DataType = typeof(int)},
                            new DataColumn(){ ColumnName = "cost",  DataType = typeof(decimal)},
                            new DataColumn(){ ColumnName = "rate",  DataType = typeof(decimal) },
                            new DataColumn(){ ColumnName = "costRMB",DataType = typeof(decimal)},
                        });
                        dtdac1.TableName = "tb1";
                        dtdac2.TableName = "tb2";
                        decimal dac1totalPrice = 0.00M, dac2totalPrice = 0.00M;
                        foreach (var item in dac1)
                        {
                            item.Place = GetEnterExitCostExportCity(placeData, item.NationalTravelFeeId);
                            if (place.Contains(item.Place))
                            {
                                continue;
                            }
                            DataRow row = dtdac1.NewRow();

                            row["city"] = item.Place;

                            string currency = _CurrDatas.Find(it => it.Id == item.Currency)?.Remark ?? "Unknwon";
                            decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
                            row["curr"] = currency;
                            row["rate"] = rate.ToString("#0.0000");
                            row["criterion"] = item.Cost.ToString("#0.00");
                            row["number"] = 1;
                            row["day"] = dac1.FindAll(x => x.NationalTravelFeeId == item.NationalTravelFeeId).Count;

                            //row["costRMB"] = rbmPrice;

                            dtdac1.Rows.Add(row);
                            place.Add(item.Place);
                        }

                        place = new List<string>();
                        foreach (var item in dac2)
                        {
                            item.Place = GetEnterExitCostExportCity(placeData, item.NationalTravelFeeId);
                            if (place.Contains(item.Place))
                            {
                                continue;
                            }
                            DataRow row = dtdac2.NewRow();
                            row["city"] = item.Place;
                            string currency = _CurrDatas.Find(it => it.Id == item.Currency)?.Remark ?? "Unknwon";
                            decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
                            row["curr"] = currency;
                            row["rate"] = rate.ToString("#0.0000");
                            row["criterion"] = item.Cost.ToString("#0.00");
                            row["number"] = 1;
                            row["day"] = dac2.FindAll(x => x.NationalTravelFeeId == item.NationalTravelFeeId).Count;
                            //row["cost"] = item.SubTotal;

                            //row["costRMB"] = rbmPrice;

                            dtdac2.Rows.Add(row);
                            place.Add(item.Place);
                            //dac2totalPrice += rbmPrice;
                        }

                        dac1totalPrice = dac1.Sum(it => it.SubTotal);
                        dac2totalPrice = dac2.Sum(it => it.SubTotal);

                        designer.SetDataSource("dac1totalPrice", dac1totalPrice.ToString("#0.00"));
                        designer.SetDataSource("dac2totalPrice", dac2totalPrice);
                        designer.SetDataSource("cityTranffic", @$"其中：国外城市间机票费: {enterExitCosts.CityTranffic.ToString("#0.00")} 元");
                        designer.SetDataSource("sumCityTranffic", @$"{enterExitCosts.CityTranffic.ToString("#0.00")} ");


                        string cell4Str = $"  4.国际旅费：经济舱：{enterExitCosts.AirJJ.ToString("#0.00")} 元/人,公务舱：{enterExitCosts.AirGW.ToString("#0.00")} 元/人";
                        string cellStr = $"  5.其他费用（";
                        if (enterExitCosts.Visa > 0) cellStr += $"签证费：{enterExitCosts.Visa.ToString("#0.00")}元，";
                        if (enterExitCosts.YiMiao > 0) cellStr += $"疫苗费：{enterExitCosts.YiMiao.ToString("#0.00")}元，";
                        if (enterExitCosts.HeSuan > 0) cellStr += $"核酸费：{enterExitCosts.HeSuan.ToString("#0.00")}元，";
                        if (enterExitCosts.Safe > 0) cellStr += $"保险费：{enterExitCosts.Safe.ToString("#0.00")}元，";
                        if (enterExitCosts.Ticket > 0) cellStr += $"参展门票费：{enterExitCosts.Ticket.ToString("#0.00")}元，";
                        if (enterExitCosts.Service > 0) cellStr += $"服务费：{enterExitCosts.Service.ToString("#0.00")}元，";
                        if (cellStr.Length > 8)
                        {
                            cellStr = cellStr.Substring(0, cellStr.Length - 1);
                        }
                        cellStr += "）";


                        decimal otherFee = enterExitCosts.Visa + enterExitCosts.YiMiao + enterExitCosts.HeSuan + enterExitCosts.Safe + enterExitCosts.Ticket + enterExitCosts.Service;
                        decimal s = dac1totalPrice + dac2totalPrice + enterExitCosts.OutsideJJPay + enterExitCosts.OutsideGWPay + otherFee;

                        decimal pxFee = dac4.Sum(it => it.Cost);
                        decimal glvFee = enterExitCosts.OutsideJJPay + enterExitCosts.OutsideGWPay;
                        string celllastStr1 = "";
                        if (dac1totalPrice > 0) celllastStr1 += $"住宿费 {dac1totalPrice.ToString("#0.00")} 元";
                        if (dac2totalPrice > 0) celllastStr1 += $"，伙食费和公杂费 {dac2totalPrice.ToString("#0.00")} 元";
                        if (pxFee > 0) celllastStr1 += $"，培训费 {pxFee.ToString("#0.00")} 元";
                        celllastStr1 += $"，国际旅费       元";
                        if (otherFee > 0) celllastStr1 += $"，其他费用 {otherFee.ToString("#0.00")} 元";

                        string celllastStr = $"    经审核，{celllastStr1}，本次出国经费预算合计为         元。其中：市本级安排      。";

                        designer.SetDataSource("cell4Str", cell4Str);
                        designer.SetDataSource("cellStr", cellStr);
                        designer.SetDataSource("cellSum", (enterExitCosts.Visa + enterExitCosts.Safe).ToString("#0.00"));
                        designer.SetDataSource("cellSum4", (enterExitCosts.OutsideJJPay + enterExitCosts.OutsideGWPay).ToString("#0.00"));
                        designer.SetDataSource("celllastStr", celllastStr);

                        Workbook wb = designer.Workbook;
                        var sheet = wb.Worksheets[0];

                        //绑定datatable数据集
                        designer.SetDataSource(dtdac1);
                        designer.SetDataSource(dtdac2);
                        designer.Process();

                        var rowStart = dtdac1.Rows.Count;
                        while (rowStart > 0)
                        {
                            sheet.Cells[startIndex, 8].Formula = $"=G{startIndex + 1} * H{startIndex + 1}";
                            sheet.Cells[startIndex, 6].Formula = $"=E{startIndex + 1} * F{startIndex + 1} * D{startIndex + 1}";
                            startIndex++;
                            rowStart--;
                        }

                        sheet.Cells[startIndex, 8].Formula = $"=SUM(I{startIndexcopy + 1}: I{startIndex})";
                        startIndex += 1; //总计行

                        rowStart = dtdac2.Rows.Count;
                        while (rowStart > 0)
                        {
                            sheet.Cells[startIndex, 8].Formula = $"= G{startIndex + 1} * H{startIndex + 1}";
                            sheet.Cells[startIndex, 6].Formula = $"= E{startIndex + 1} *  F{startIndex + 1} * D{startIndex + 1}";
                            startIndex++;
                            rowStart--;
                        }

                        sheet.Cells[startIndex, 8].Formula = $"=SUM(I{startIndexcopy + dtdac1.Rows.Count + 2}: I{startIndex})";

                        wb.CalculateFormula(true);

                        //模板文件名
                        string strFileName = $"{_DelegationInfo.TeamName}-四川省商务厅出国经费财政先行审核表{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls"; ;//$".xls";
                        designer.Workbook.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
                        string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;

                        _view.Data = new { Url = url };
                        _view.Msg = "成功";
                        return _view;
                    }
                    else if (dto.SubTypeId == 1066) //成都市因公临时出国任务和预算审批意见表(外专培训团专用)
                    {
                        //获取模板
                        string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/成都市因公临时出国任务和预算审批意见表.docx");
                        //载入模板
                        Document doc = new Document(tempPath);

                        DocumentBuilder builder = new DocumentBuilder(doc);

                        Dictionary<string, string> dic = new Dictionary<string, string>();

                        dic.Add("GroupName", _DelegationInfo.TeamName);
                        dic.Add("ClientUnit", _DelegationInfo.ClientUnit);

                        string missionLeader = "";  //团负责人默认接团客户名单第一个人
                        string missionLeaderJob = "";//负责人job
                        //int groupNumber = 0; //团人数
                        if (DeleClientList.Count > 0)
                        {
                            missionLeader = DeleClientList[0]?.Name ?? "";
                            missionLeaderJob = DeleClientList[0]?.Job ?? "";
                        }
                        dic.Add("MissionLeader", missionLeader); //团负责人
                        dic.Add("MissionLeaderJob", missionLeaderJob); //团负责人job
                        dic.Add("GroupNumber", _DelegationInfo.VisitPNumber.ToString()); //团人数


                        #region MyRegion
                        //if (blackCode != null && !string.IsNullOrWhiteSpace(blackCode.BlackCode))
                        //{
                        //    List<string> list = new List<string>();
                        //    try
                        //    {
                        //        var spilitArr = Regex.Split(blackCode.BlackCode, "\r\n");
                        //        foreach (var item in spilitArr)
                        //        {
                        //            var spDotandEmpty = item.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
                        //            var depCode = spDotandEmpty[2].Substring(0, 3);
                        //            var arrCode = spDotandEmpty[2].Substring(3, 3);
                        //            string depName = threeCodes.Find(it => it.Three.Equals(depCode)).City,
                        //                   arrName = threeCodes.Find(it => it.Three.Equals(arrCode)).City;

                        //            list.Add(depName);
                        //            list.Add(arrName);
                        //        }

                        //        list = list.Distinct().ToList();
                        //        dic.Add("ReturnCode", string.Join("-", list).TrimEnd('-'));
                        //    }
                        //    catch (Exception)
                        //    {
                        //        dic.Add("ReturnCode", "行程录入不正确!");
                        //    }
                        //}
                        //else
                        //{
                        //    dic.Add("ReturnCode", "未录入行程!");
                        //}

                        List<string> countrys = _dirRep.GroupSplitCountry(_DelegationInfo.VisitCountry);
                        dic.Add("ReturnCode", string.Join("、", countrys));
                        #endregion


                        //dic.Add("ReturnCodeAir", dic["ReturnCode"]);
                        //dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
                        //dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));

                        //if (dic.ContainsKey("VisitStartDate") && dic.ContainsKey("VisitEndDate"))
                        //{
                        //    TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
                        //    dic.Add("Day", sp.Days.ToString());

                        //}
                        dic.Add("Day", _DelegationInfo.VisitDays.ToString());

                        dic.Add("CultivateDay", dac4.Count.ToString()); //培训天数
                                                                        // dic.Add("VisitCountry", _DelegationInfo.VisitCountry);

                        NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
                        Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;

                        //培训人员名单
                        int cultivateRowIndex = 7;
                        foreach (var item in DeleClientList)
                        {
                            builder.MoveToCell(0, cultivateRowIndex, 0, 0);
                            builder.Write(item.Name);
                            builder.MoveToCell(0, cultivateRowIndex, 1, 0);
                            builder.Write(item.Sex == 0 ? "男" : item.Sex == 1 ? "女" : "");

                            builder.MoveToCell(0, cultivateRowIndex, 2, 0);

                            string birthDay = "";
                            if (item.Birthday != null)
                            {
                                DateTime dt = Convert.ToDateTime(item.Birthday);
                                birthDay = $"{dt.Year}.{dt.Month}";
                            }
                            builder.Write(birthDay);

                            builder.MoveToCell(0, cultivateRowIndex, 3, 0);
                            builder.Write(item.Company);

                            builder.MoveToCell(0, cultivateRowIndex, 4, 0);
                            builder.Write(item.Job);

                            cultivateRowIndex++;
                        }

                        //删除多余行
                        //cultivateRowIndex -= 2;
                        int delRows = 10 + 7 - cultivateRowIndex;
                        if (delRows > 0)
                        {
                            for (int i = 0; i < delRows; i++)
                            {

                                table1.Rows.RemoveAt(cultivateRowIndex);
                                //cultivateRowIndex++;
                            }
                        }

                        decimal hotelFeeTotal = dac1.Sum(it => it.SubTotal);//住宿费
                        dic.Add("HotelFeeTotal", hotelFeeTotal.ToString("#0.00"));
                        decimal mealsFeeTotal = dac2.Sum(it => it.SubTotal);//伙食费
                        dic.Add("MealsFeeTotal", mealsFeeTotal.ToString("#0.00"));
                        decimal miscellaneousFeeTotal = dac3.Sum(it => it.SubTotal);//公杂费
                        dic.Add("MiscellaneousFeeTotal", miscellaneousFeeTotal.ToString("#0.00"));
                        decimal trainingFeeTotal = dac4.Sum(it => it.SubTotal);//培训费
                        dic.Add("TrainingFeeTotal", trainingFeeTotal.ToString("#0.00"));
                        decimal cityTranfficFeeToatal = enterExitCosts.CityTranffic; //城市区间交通费
                        dic.Add("CityTranfficFeeToatal", cityTranfficFeeToatal.ToString("#0.00"));//
                                                                                                  //其他费用
                        decimal otherFeeTotal = enterExitCosts.Visa + enterExitCosts.Safe + enterExitCosts.Ticket + enterExitCosts.YiMiao + enterExitCosts.HeSuan + enterExitCosts.Service;
                        dic.Add("OtherFeeTotal", otherFeeTotal.ToString("#0.00"));

                        //其他费用合计
                        decimal _otherFeeTotal = hotelFeeTotal + mealsFeeTotal + miscellaneousFeeTotal + trainingFeeTotal + cityTranfficFeeToatal + otherFeeTotal;

                        decimal _jjcFeeToatal = enterExitCosts.AirJJ + _otherFeeTotal; //经济舱
                        decimal _gwcFeeToatal = enterExitCosts.AirGW + _otherFeeTotal; //公务舱

                        //公务舱合计

                        //国际旅费

                        //string outsideJJ = "";
                        //string allPriceJJ = "";
                        if (enterExitCosts.SumJJC == 1 && enterExitCosts.SumGWC == 0)
                        {
                            dic.Add("AirFeeTotal", enterExitCosts.AirJJ.ToString("#0.00"));
                            dic.Add("FeeTotal", _jjcFeeToatal.ToString("#0.00"));
                        }
                        if (enterExitCosts.SumGWC == 1 && enterExitCosts.SumJJC == 0)
                        {
                            dic.Add("AirFeeTotal", enterExitCosts.AirGW.ToString("#0.00"));
                            dic.Add("FeeTotal", _gwcFeeToatal.ToString("#0.00"));
                        }

                        if (enterExitCosts.SumJJC == 1 && enterExitCosts.SumGWC == 1)
                        {
                            string airFeeTotalStr = string.Format(@$"经济舱:{enterExitCosts.AirJJ.ToString("#0.00")} 公务舱:{enterExitCosts.AirGW.ToString("#0.00")}");
                            dic.Add("AirFeeTotal", airFeeTotalStr);
                            string feeTotalStr = string.Format(@$"经济舱:{_jjcFeeToatal.ToString("#0.00")} 公务舱:{_gwcFeeToatal.ToString("#0.00")}");
                            dic.Add("FeeTotal", feeTotalStr);
                        }

                        foreach (var key in dic.Keys)
                        {
                            builder.MoveToBookmark(key);
                            builder.Write(dic[key]);
                        }

                        //模板文件名
                        string strFileName = $"{_DelegationInfo.TeamName}-成都市因公临时出国任务和预算审批意见表(外专培训团专用).docx";
                        doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
                        string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;

                        _view.Data = new { Url = url };
                        _view.Msg = "成功";
                        return _view;
                    }
                }
                else if (dto.ExportType == 2) //表格
                {
                    //利用键值对存放数据
                    Dictionary<string, string> dic = new Dictionary<string, string>();

                    dic.Add("VisitStartDate", _DelegationInfo.VisitStartDate.ToString("yyyy年MM月dd日"));
                    dic.Add("VisitEndDate", _DelegationInfo.VisitEndDate.ToString("yyyy年MM月dd日"));

                    TimeSpan sp = _DelegationInfo.VisitEndDate.Subtract(_DelegationInfo.VisitStartDate);
                    dic.Add("Day", sp.Days.ToString());

                    dic.Add("VisitCountry", _DelegationInfo.VisitCountry);

                    if (dto.SubTypeId == 1008) //1008(派员单位出(境)任务和预算审批意见表)
                    {
                        //获取模板
                        string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/派员单位出(境)任务和预算审批意见表.docx");
                        //载入模板
                        Document doc = new Document(tempPath);
                        DocumentBuilder builder = new DocumentBuilder(doc);

                        dic.Add("TeamName", _DelegationInfo.TeamName);
                        dic.Add("ClientUnit", _DelegationInfo.ClientUnit);
                        //dic.Add("TellPhone", _DelegationInfo.TellPhone);

                        string missionLeaderName = "",
                               missionLeaderJob = "";

                        if (DeleClientList.Count > 0)
                        {
                            missionLeaderName = DeleClientList[0].Name;
                            missionLeaderJob = DeleClientList[0].Job;
                        }

                        dic.Add("MissionLeaderName", missionLeaderName);
                        dic.Add("MissionLeaderJob", missionLeaderJob);

                        dic.Add("VisitPNumber", _DelegationInfo.VisitPNumber.ToString());
                        dic.Add("VisitPurpose", _DelegationInfo.VisitPurpose);

                        NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
                        Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;
                        int rowCount = 10;//总人数行
                        int startRowIndex = 7; //起始行
                        for (int i = 0; i < DeleClientList.Count; i++)
                        {
                            builder.MoveToCell(0, startRowIndex, 0, 0);
                            builder.Write(DeleClientList[i].Name); //出国人员姓名
                            builder.MoveToCell(0, startRowIndex, 1, 0);
                            string sex = DeleClientList[i].Sex == 0 ? "男" : DeleClientList[i].Sex == 1 ? "女" : "";

                            builder.Write(sex);//性别
                            builder.MoveToCell(0, startRowIndex, 2, 0);
                            builder.Write(Convert.ToDateTime(DeleClientList[i].Birthday).ToString("yyyy年MM月dd日"));//出生年月
                            builder.MoveToCell(0, startRowIndex, 3, 0);
                            builder.Write(DeleClientList[i].Company);//工作单位
                            builder.MoveToCell(0, startRowIndex, 4, 0);
                            builder.Write(DeleClientList[i].Job);//职务及级别
                            builder.MoveToCell(0, startRowIndex, 5, 0);
                            builder.Write("");//人员属性
                            builder.MoveToCell(0, startRowIndex, 6, 0);
                            builder.Write("");//上次出国时间
                            startRowIndex++;
                        }
                        int nullRow = rowCount - DeleClientList.Count;//空行
                        for (int i = 0; i < nullRow; i++)
                        {
                            table1.Rows.Remove(table1.Rows[startRowIndex]);
                        }

                        foreach (var key in dic.Keys)
                        {
                            builder.MoveToBookmark(key);
                            builder.Write(dic[key]);
                        }
                        //模板文件名
                        string strFileName = $"派员单位出(境)任务和预算审批意见表.docx";
                        doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
                        string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;

                        _view.Data = new { Url = url };
                        _view.Msg = "成功";
                        return _view;

                    }
                    else if (dto.SubTypeId == 1009)//1009(省级单位出(境)经费报销单)
                    {
                        //获取模板
                        string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/省级单位出(境)经费报销单.docx");
                        //载入模板
                        Document doc = new Document(tempPath);
                        DocumentBuilder builder = new DocumentBuilder(doc);

                        dic.Add("GroupClient", _DelegationInfo.ClientUnit);

                        var Names = string.Join("、", DeleClientList.Select(x => x.Name)).TrimEnd('、');
                        dic.Add("Names", Names);

                        int accommodationRows = 12, foodandotherRows = 12;
                        var Dac1currCn = dac1.GroupBy(x => x.Currency).Select(x => x.Key).ToList();
                        var Dac2currCn = dac2.GroupBy(x => x.Currency).Select(x => x.Key).ToList();

                        NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);
                        Aspose.Words.Tables.Table table1 = allTables[0] as Aspose.Words.Tables.Table;


                        int accommodationStartIndex = 6;
                        decimal dac1totalPrice = 0.00M;
                        foreach (var dac in dac1)
                        {
                            if (dac.SubTotal == 0)
                            {
                                continue;
                            }

                            //builder.MoveToCell(0, accommodationStartIndex, 0, 0);
                            //builder.Write(DeleClientList[i].LastName + DeleClientList[i].Name); 
                            builder.MoveToCell(0, accommodationStartIndex, 1, 0);
                            builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));//城市
                            builder.MoveToCell(0, accommodationStartIndex, 2, 0);

                            string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknwon";
                            builder.Write(currency);//币种
                            builder.MoveToCell(0, accommodationStartIndex, 3, 0);
                            builder.Write(dac.Cost.ToString("#0.00"));//标准
                            builder.MoveToCell(0, accommodationStartIndex, 4, 0);
                            builder.Write("");//人数
                            builder.MoveToCell(0, accommodationStartIndex, 5, 0);
                            builder.Write("");//天数
                            builder.MoveToCell(0, accommodationStartIndex, 6, 0);
                            builder.Write(dac.SubTotal.ToString("#0.00"));//小计
                            builder.MoveToCell(0, accommodationStartIndex, 7, 0);

                            decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
                            builder.Write(rate.ToString("#0.0000"));//汇率
                            builder.MoveToCell(0, accommodationStartIndex, 8, 0);

                            decimal rbmPrice = rate * dac.SubTotal;
                            builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
                            accommodationStartIndex++;
                            dac1totalPrice += rbmPrice;
                        }




                        dic.Add("dac1totalPrice", dac1totalPrice.ToString("#0.00"));


                        builder.MoveToCell(0, accommodationStartIndex, 1, 0);
                        builder.Write("小计");
                        builder.MoveToCell(0, accommodationStartIndex, 8, 0);
                        builder.Write(dac1totalPrice.ToString("#0.00"));

                        accommodationStartIndex++;


                        int nullRow = accommodationRows - dac1.Count;
                        //删除空行
                        //if (nullRow > 0)
                        //{
                        //    int rowIndex = accommodationStartIndex;
                        //    for (int i = 0; i < nullRow; i++)
                        //    {
                        //        Row row = table1.Rows[rowIndex];
                        //        row.Remove();        
                        //        rowIndex++;
                        //    }
                        //}

                        if (dac2.Count == dac3.Count)//国家 币种 金额
                        {
                            for (int i = 0; i < dac2.Count; i++)
                            {
                                dac2[i].SubTotal = dac2[i].SubTotal + dac3[i].SubTotal; //小计
                                dac2[i].Cost = dac3[i].Cost + dac2[i].Cost; //标准
                            }
                        }

                        int foodandotherStartIndex = 19;//
                        decimal dac2totalPrice = 0.00M;
                        foreach (var dac in dac2)
                        {
                            if (dac.SubTotal == 0)
                            {
                                continue;
                            }
                            //foodandotherStartIndex = 12;
                            builder.MoveToCell(0, foodandotherStartIndex, 1, 0);
                            builder.Write(GetEnterExitCostExportCity(placeData, dac.NationalTravelFeeId));//城市
                            builder.MoveToCell(0, foodandotherStartIndex, 2, 0);

                            string currency = _CurrDatas.Find(it => it.Id == dac.Currency)?.Remark ?? "Unknwon";
                            builder.Write(currency);//币种
                            builder.MoveToCell(0, foodandotherStartIndex, 3, 0);
                            builder.Write(dac.Cost.ToString("#0.00"));//标准
                            builder.MoveToCell(0, foodandotherStartIndex, 4, 0);
                            builder.Write("");//人数
                            builder.MoveToCell(0, foodandotherStartIndex, 5, 0);
                            builder.Write("");//天数
                            builder.MoveToCell(0, foodandotherStartIndex, 6, 0);
                            builder.Write(dac.SubTotal.ToString("#0.00"));//小计
                            builder.MoveToCell(0, foodandotherStartIndex, 7, 0);

                            decimal rate = rateDatas.Find(it => it.CurrencyName == currency)?.Rate ?? 0.00M;
                            builder.Write(rate.ToString("#0.0000"));//汇率
                            builder.MoveToCell(0, foodandotherStartIndex, 8, 0);

                            decimal rbmPrice = rate * dac.SubTotal;
                            builder.Write(rbmPrice.ToString("#0.00"));//折合人民币
                            foodandotherStartIndex++;
                            dac2totalPrice += rbmPrice;
                        }

                        dic.Add("dac2totalPrice", dac2totalPrice.ToString("#0.00"));
                        //删除空行
                        if (dac2.Count < foodandotherRows)
                        {
                            //int nullRow = accommodationRows - dac2.Count;
                            //while (table2.Rows.Count > dac2.Count)
                            //{
                            //    table2.Rows.RemoveAt(dac2.Count);
                            //}
                        }

                        dic.Add("CityTranffic", enterExitCosts.CityTranffic.ToString("#0.00"));

                        string otherFeeStr = "";
                        if (enterExitCosts.Visa > 0) otherFeeStr += $"签证费： {enterExitCosts.Visa.ToString("#0.00")} 元，";
                        if (enterExitCosts.Safe > 0) otherFeeStr += $"保险费： {enterExitCosts.Safe.ToString("#0.00")} 元，";
                        if (enterExitCosts.Ticket > 0) otherFeeStr += $"参展门票费： {enterExitCosts.Ticket.ToString("#0.00")} 元，";

                        if (otherFeeStr.Length > 0)
                        {
                            otherFeeStr = otherFeeStr.Substring(0, otherFeeStr.Length - 1);
                            otherFeeStr = $"({otherFeeStr})";

                            dic.Add("OtherFeeStr", otherFeeStr);
                        }

                        foreach (var key in dic.Keys)
                        {
                            builder.MoveToBookmark(key);
                            builder.Write(dic[key]);
                        }
                        //模板文件名
                        string strFileName = $"省级单位出(境)经费报销单.docx";
                        doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);
                        string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;

                        _view.Data = new { Url = url };
                        _view.Msg = "成功";
                        return _view;
                    }
                }
                else if (dto.ExportType == 3)
                {
                    if (dto.SubTypeId == 1) //团组成员名单
                    {
                        if (DeleClientList.Count < 1)
                        {
                            _view.Msg = "团组成员暂未录入!!!";
                            return _view;
                        }

                        //获取模板
                        string tempPath = (AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/Temp/dwon_团组人员列表模板.doc");
                        //载入模板
                        Document doc = new Document(tempPath);
                        DocumentBuilder builder = new DocumentBuilder(doc);

                        //获取word里所有表格
                        NodeCollection allTables = doc.GetChildNodes(NodeType.Table, true);

                        //获取所填表格的序数
                        Aspose.Words.Tables.Table tableOne = allTables[0] as Aspose.Words.Tables.Table;

                        var rowStart = tableOne.Rows[0]; //获取第1行

                        //循环赋值
                        for (int i = 0; i < DeleClientList.Count; i++)
                        {
                            builder.MoveToCell(0, i + 1, 0, 0);
                            builder.Write(DeleClientList[i].Name);

                            builder.MoveToCell(0, i + 1, 1, 0);
                            string sex = DeleClientList[i].Sex == 0 ? "男" : DeleClientList[i].Sex == 1 ? "女" : "";
                            builder.Write(sex);

                            builder.MoveToCell(0, i + 1, 2, 0);
                            builder.Write(Convert.ToDateTime(DeleClientList[i].Birthday).ToString("yyyy年MM月dd日"));

                            builder.MoveToCell(0, i + 1, 3, 0);
                            builder.Write(DeleClientList[i].Company);

                            builder.MoveToCell(0, i + 1, 4, 0);
                            builder.Write(DeleClientList[i].Job);
                        }

                        //删除多余行
                        while (tableOne.Rows.Count > DeleClientList.Count + 1)
                        {
                            tableOne.Rows.RemoveAt(DeleClientList.Count + 1);
                        }

                        string strFileName = $"{_DelegationInfo.TeamName}组团人员名单({DateTime.Now.ToString("yyyyMMddHHmmss")}).doc";
                        //C:/Server/File/OA2023/Office/Word/EnterExitCost/File/
                        //C:\Server\File\OA2023\Office\Word\EnterExitCost\File\
                        doc.Save(AppSettingsHelper.Get("WordBasePath") + "EnterExitCost/File/" + strFileName);

                        string url = AppSettingsHelper.Get("WordBaseUrl") + "Office/Word/EnterExitCost/File/" + strFileName;

                        _view.Data = new { Url = url };
                        _view.Msg = "成功";
                        return _view;
                    }
                }
            }
            catch (Exception ex)
            {
                _view.Code = StatusCodes.Status500InternalServerError;
                _view.Msg = ex.Message;

                return _view;
            }
            return _view;
        }


        #endregion

        #region 机票费用提示
        /// <summary>
        /// 三公机票费用提示
        /// </summary>
        /// <param name="diId"></param>
        /// <returns></returns>
        public static async Task<EntryAndExitTipsView[]> EntryAndExitTips(int diId)
        {
            if (diId < 1) return Array.Empty<EntryAndExitTipsView>();

            var airFees = await _sqlSugar.Queryable<Air_TicketBlackCode>().Where(a => a.IsDel == 0 && a.DiId == diId).ToListAsync();

            if (airFees.Any())
            {

                var result = new List<EntryAndExitTipsView>();


                foreach (var x in airFees)
                {
                    string tripDesc = "";
                    if (!string.IsNullOrEmpty(x.BlackCode))
                    {
                        var redisKeyName = string.Format("AirTripDesc_{0}_{1}", x.DiId, x.Id);
                        tripDesc = await KIMIAirTripCodeRec(redisKeyName, x.BlackCode);
                    }


                    result.Add(new EntryAndExitTipsView
                    {
                        jjcCurrentRate = x.ECPrice,
                        gwcCurrentRate = x.BCPrice,
                        tdcCurrentRate = x.FCPrice,
                        remark = $"经济舱全价：{x.ECPrice.ToString("#0.00")} 元/人 公务舱全价：{x.BCPrice.ToString("#0.00")} 元/人 头等舱全价：{x.FCPrice.ToString("#0.00")} 元/人",
                        TripDesc = tripDesc
                    });
                }
                return result.OrderByDescending(x => x.jjcCurrentRate + x.gwcCurrentRate + x.tdcCurrentRate).ToArray();
            }

            return Array.Empty<EntryAndExitTipsView>();
        }

        /// <summary>
        /// 机票行程代码识别
        /// </summary>
        /// <param name="redisKeyName">redis缓存名称(名称定义格式：AirTripDesc_Diid_Id)</param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public static async Task<string> KIMIAirTripCodeRec(string redisKeyName, string msg)
        {
            string result = string.Empty;

            if (!string.IsNullOrEmpty(redisKeyName))
            {
                result = await RedisRepository.RedisFactory.CreateRedisRepository().StringGetAsync<string>(redisKeyName);//string 取
                if (!string.IsNullOrEmpty(result)) return result;
            }

            if (string.IsNullOrEmpty(msg)) return result;

            KiMiApiClient KiMiApi = new KiMiApiClient();

            var kimiMsgs = new List<SeedMessages>() {
                    new SeedMessages { Role = KimiRole.system, Content = "你是 Kimi，由 Moonshot AI 提供的人工智能助手，你更擅长中文和英文的对话。你会为用户提供安全，有帮助，准确的回答。同时，你会拒绝一切涉及恐怖主义，种族歧视，黄色暴力等问题的回答。Moonshot AI 为专有名词，不可翻译成其他语言。" },
                    new SeedMessages { Role = KimiRole.user, Content = "请将提供的三字码行程信息翻译成中文，每条信息独立一行，并在次日到达的时间后加上“+1”以表示次日。日期（星期）格式请按照“04月14日（星期一）”标注。航站楼信息请按照“T1”、“T2”、“ ”等格式标注。只输出正文数据，不要除正文数据外的任何文字以及符号。格式示例如下：\r\n1. CA445 05月17日(星期六) 成都天府 T1 - 米兰马尔彭萨 T1 01:50 06:55" },
                    new SeedMessages { Role = KimiRole.user, Content = msg }
                };

            var kimiApiResult = await KiMiApi.SeedMessage(kimiMsgs);
            var kimiApiResult_JObject = JObject.Parse(kimiApiResult);
            result = kimiApiResult_JObject["content"].ToString();
            if (!string.IsNullOrEmpty(result))
            {
                TimeSpan ts = TimeSpan.FromHours(2); //过期时间 2 小时
                await RedisRepository.RedisFactory.CreateRedisRepository().StringSetAsync<string>(redisKeyName, result, ts);//string 存
            }

            return result;
        }

        #endregion
        #endregion

        #region Excel导出服务
        #region 类
        public class FileExportSettings
        {
            public string ExportBasePath { get; set; } = "wwwroot/exports";
            public string DownloadBaseUrl { get; set; } = "/exports";
            public int RetentionDays { get; set; } = 7;
            public int MaxFileSizeMB { get; set; } = 50;
            public string ExcelTemplatesPath { get; set; } = "Templates/Excel";
        }

        public class ExportResult
        {
            public bool Success { get; set; }
            public string FilePath { get; set; }
            public string DownloadUrl { get; set; }
            public string FileName { get; set; }
            public long FileSize { get; set; }
            public string MimeType { get; set; } = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            public string ErrorMessage { get; set; }
            public DateTime GeneratedAt { get; set; } = DateTime.Now;
        }

        public class ExportRequest
        {
            public IEnumerable<object> Data { get; set; }
            public string FileName { get; set; }
            public string SheetName { get; set; } = "Sheet1";
            public string TemplateType { get; set; } = "default";
            public Dictionary<string, string> CustomHeaders { get; set; }
        }

        public class ExportResponse
        {
            public string FilePath { get; set; }
            public string DownloadUrl { get; set; }
            public string FileName { get; set; }
            public long FileSize { get; set; }
            public DateTime GeneratedAt { get; set; }
            public string Message { get; set; }
        }
        #endregion



        public static async Task<ExportResult> ExportToExcel<T>(IEnumerable<T> data, string fileName, string sheetName = "Sheet1", string templateType = "default")
        {
            try
            {
                byte[] excelBytes = templateType.ToLower() switch
                {
                    "styled" => GenerateStyledExcel(data, sheetName),
                    "with_formulas" => GenerateExcelWithFormulas(data, sheetName),
                    //"with_charts" => GenerateExcelWithCharts(data, sheetName),
                    _ => GenerateDefaultExcel(data, sheetName)
                };

                var filePath = await SaveFileAsync(excelBytes, fileName, "excel");
                var downloadUrl = $"wwwroot/exports/{filePath}";

                return new ExportResult
                {
                    Success = true,
                    FilePath = filePath,
                    DownloadUrl = downloadUrl,
                    FileName = Path.GetFileName(filePath),
                    FileSize = excelBytes.Length,
                    MimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                };
            }
            catch (Exception ex)
            {
                return new ExportResult
                {
                    Success = false,
                    ErrorMessage = ex.Message
                };
            }
        }

        public static async Task<ExportResult> ExportWithTemplate<T>(IEnumerable<T> data, byte[] templateBytes, string fileName, string sheetName = "Sheet1")
        {
            try
            {
                using var templateStream = new MemoryStream(templateBytes);
                using var workbook = new Workbook(templateStream);

                var worksheet = workbook.Worksheets[sheetName] ?? workbook.Worksheets[0];

                // 查找数据起始行（可以根据模板中的标记来定位）
                int startRow = FindDataStartRow(worksheet);

                // 填充数据
                FillWorksheetWithData(worksheet, data, startRow);

                using var outputStream = new MemoryStream();
                workbook.Save(outputStream, Aspose.Cells.SaveFormat.Xlsx);
                var excelBytes = outputStream.ToArray();

                var filePath = await SaveFileAsync(excelBytes, fileName, "excel/templates");
                var downloadUrl = $"wwwroot/exports/exports/{filePath}";

                return new ExportResult
                {
                    Success = true,
                    FilePath = filePath,
                    DownloadUrl = downloadUrl,
                    FileName = Path.GetFileName(filePath),
                    FileSize = excelBytes.Length,
                    MimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                };
            }
            catch (Exception ex)
            {
                return new ExportResult
                {
                    Success = false,
                    ErrorMessage = ex.Message
                };
            }
        }

        public static async Task<ExportResult> ExportMultipleSheets<T>(Dictionary<string, IEnumerable<T>> sheetsData, string fileName)
        {
            try
            {
                using var workbook = new Workbook();
                workbook.Worksheets.Clear(); // 清除默认工作表

                foreach (var sheet in sheetsData)
                {
                    var worksheet = workbook.Worksheets.Add(sheet.Key);
                    var data = sheet.Value;

                    if (data != null && data.Any())
                    {
                        FillWorksheetWithData(worksheet, data, 0);
                        worksheet.AutoFitColumns();
                    }
                }

                using var stream = new MemoryStream();
                workbook.Save(stream, Aspose.Cells.SaveFormat.Xlsx);
                var excelBytes = stream.ToArray();

                var filePath = await SaveFileAsync(excelBytes, fileName, "excel/multi-sheets");
                var downloadUrl = $"wwwroot/exports/exports/{filePath}";

                return new ExportResult
                {
                    Success = true,
                    FilePath = filePath,
                    DownloadUrl = downloadUrl,
                    FileName = Path.GetFileName(filePath),
                    FileSize = excelBytes.Length,
                    MimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                };
            }
            catch (Exception ex)
            {
                return new ExportResult
                {
                    Success = false,
                    ErrorMessage = ex.Message
                };
            }
        }

        public static async Task<ExportResult> ExportWithCustomHeaders<T>(IEnumerable<T> data, Dictionary<string, string> headers, string fileName, string sheetName = "Sheet1")
        {
            try
            {
                using var workbook = new Workbook();
                var worksheet = workbook.Worksheets[0];
                worksheet.Name = sheetName;

                // 设置自定义表头
                int colIndex = 0;
                foreach (var header in headers)
                {
                    worksheet.Cells[0, colIndex].PutValue(header.Value);
                    colIndex++;
                }

                // 填充数据
                if (data != null && data.Any())
                {
                    int rowIndex = 1;
                    var properties = typeof(T).GetProperties();

                    foreach (var item in data)
                    {
                        colIndex = 0;
                        foreach (var header in headers)
                        {
                            var property = properties.FirstOrDefault(p => p.Name == header.Key);
                            if (property != null)
                            {
                                var value = property.GetValue(item);
                                worksheet.Cells[rowIndex, colIndex].PutValue(value);
                            }
                            colIndex++;
                        }
                        rowIndex++;
                    }
                }

                worksheet.AutoFitColumns();

                using var stream = new MemoryStream();
                workbook.Save(stream, Aspose.Cells.SaveFormat.Xlsx);
                var excelBytes = stream.ToArray();

                var filePath = await SaveFileAsync(excelBytes, fileName, "excel/custom-headers");
                var downloadUrl = $"filePath";

                return new ExportResult
                {
                    Success = true,
                    FilePath = filePath,
                    DownloadUrl = downloadUrl,
                    FileName = Path.GetFileName(filePath),
                    FileSize = excelBytes.Length,
                    MimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                };
            }
            catch (Exception ex)
            {
                return new ExportResult
                {
                    Success = false,
                    ErrorMessage = ex.Message
                };
            }
        }

        private static byte[] GenerateDefaultExcel<T>(IEnumerable<T> data, string sheetName)
        {
            using var workbook = new Workbook();
            var worksheet = workbook.Worksheets[0];
            worksheet.Name = sheetName;

            if (data != null && data.Any())
            {
                FillWorksheetWithData(worksheet, data, 0);
                worksheet.AutoFitColumns();
            }

            return SaveWorkbookToBytes(workbook);
        }

        private static byte[] GenerateStyledExcel<T>(IEnumerable<T> data, string sheetName)
        {
            using var workbook = new Workbook();
            var worksheet = workbook.Worksheets[0];
            worksheet.Name = sheetName;

            if (data != null && data.Any())
            {
                FillWorksheetWithData(worksheet, data, 0);

                // 设置样式
                var headerStyle = workbook.CreateStyle();
                headerStyle.Pattern = BackgroundType.Solid;
                headerStyle.ForegroundColor = System.Drawing.Color.LightGray;
                headerStyle.Font.IsBold = true;
                headerStyle.HorizontalAlignment = TextAlignmentType.Center;

                var properties = typeof(T).GetProperties();
                Aspose.Cells.Range headerRange = worksheet.Cells.CreateRange(0, 0, 1, properties.Length);
                headerRange.ApplyStyle(headerStyle, new StyleFlag { All = true });

                worksheet.AutoFitColumns();
            }

            return SaveWorkbookToBytes(workbook);
        }

        private static byte[] GenerateExcelWithFormulas<T>(IEnumerable<T> data, string sheetName)
        {
            using var workbook = new Workbook();
            var worksheet = workbook.Worksheets[0];
            worksheet.Name = sheetName;

            if (data != null && data.Any())
            {
                FillWorksheetWithData(worksheet, data, 0);

                // 添加公式（例如：求和公式）
                var properties = typeof(T).GetProperties();
                int lastRow = data.Count() + 1;

                for (int i = 0; i < properties.Length; i++)
                {
                    if (IsNumericType(properties[i].PropertyType))
                    {
                        worksheet.Cells[lastRow, i].Formula = $"=SUM({worksheet.Cells[1, i].Name}:{worksheet.Cells[lastRow - 1, i].Name})";
                    }
                }

                worksheet.AutoFitColumns();
            }

            return SaveWorkbookToBytes(workbook);
        }

        private static void FillWorksheetWithData<T>(Worksheet worksheet, IEnumerable<T> data, int startRow)
        {
            var properties = typeof(T).GetProperties();

            // 设置表头
            for (int i = 0; i < properties.Length; i++)
            {
                worksheet.Cells[startRow, i].PutValue(properties[i].Name);
            }

            // 填充数据
            int rowIndex = startRow + 1;
            foreach (var item in data)
            {
                for (int colIndex = 0; colIndex < properties.Length; colIndex++)
                {
                    var value = properties[colIndex].GetValue(item);
                    worksheet.Cells[rowIndex, colIndex].PutValue(value);
                }
                rowIndex++;
            }
        }

        private static int FindDataStartRow(Worksheet worksheet)
        {
            // 在实际项目中，可以根据模板中的特定标记来定位数据起始行
            // 例如查找 "{{DATA_START}}" 这样的标记
            for (int row = 0; row < 50; row++)
            {
                for (int col = 0; col < 10; col++)
                {
                    var value = worksheet.Cells[row, col].StringValue;
                    if (value == "{{DATA_START}}")
                    {
                        worksheet.Cells[row, col].PutValue(""); // 清空标记
                        return row + 1;
                    }
                }
            }
            return 1; // 默认从第2行开始
        }

        private static byte[] SaveWorkbookToBytes(Workbook workbook)
        {
            using var stream = new MemoryStream();
            workbook.Save(stream, Aspose.Cells.SaveFormat.Xlsx);
            return stream.ToArray();
        }

        private static bool IsNumericType(Type type)
        {
            return Type.GetTypeCode(type) switch
            {
                TypeCode.Byte or TypeCode.SByte or TypeCode.UInt16 or TypeCode.UInt32 or
                TypeCode.UInt64 or TypeCode.Int16 or TypeCode.Int32 or TypeCode.Int64 or
                TypeCode.Decimal or TypeCode.Double or TypeCode.Single => true,
                _ => false
            };
        }


        public static async Task<string> SaveFileAsync(byte[] fileData, string fileName, string subFolder = "")
        {
            try
            {
                var basePath = "wwwroot/exports/exports";
                var folderPath = Path.Combine(basePath, subFolder);
                Directory.CreateDirectory(folderPath);

                var uniqueFileName = $"{Path.GetFileNameWithoutExtension(fileName)}_{Guid.NewGuid():N}{Path.GetExtension(fileName)}.xlsx";
                var filePath = Path.Combine(folderPath, uniqueFileName);

                await System.IO.File.WriteAllBytesAsync(filePath, fileData);

                var relativePath = Path.Combine(basePath, subFolder, uniqueFileName).Replace("\\", "/");

                return relativePath;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        #endregion

        #region 动态构架word表格

        /// <summary>
        /// 设置表格样式
        /// </summary>
        /// <param name="builder"></param>
        public static void SetupDocumentStyles(DocumentBuilder builder)
        {
            builder.Font.Name = "微软雅黑";
            builder.Font.Size = 10;
            builder.ParagraphFormat.SpaceAfter = 0;
        }

        /// <summary>
        /// 添加标题
        /// </summary>
        /// <param name="builder"></param>
        /// <param name="title"></param>
        public static void AddTitle(DocumentBuilder builder, string title)
        {
            // 主标题
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
            builder.Font.Size = 16;
            builder.Font.Bold = true;
            builder.Font.Color = System.Drawing.Color.DarkBlue;
            builder.Writeln(title);

            // 空行
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Left;
            builder.Font.Size = 10;
            builder.Font.Bold = false;
            builder.Font.Color = System.Drawing.Color.Black;
            builder.Writeln();
        }

        /// <summary>
        /// 生成酒机票表格信息
        /// </summary>
        /// <param name="builder"></param>
        /// <param name="airInfos"></param>
        /// <param name="footerText"></param>
        public static void AddAirInfoSections(DocumentBuilder builder, List<Grp_AirTicketReservations> airInfos, string? footerText)
        {
            if (airInfos == null || !airInfos.Any())
            {
                builder.Writeln("暂无机票航班信息。");
                return;
            }

            int airCount = 0;
            const int maxAirPerPage = 3; // 每页最多显示3个酒店，确保有足够空间

            for (int i = 0; i < airInfos.Count; i++)
            {
                var info = airInfos[i];

                // 检查是否需要分页
                if (airCount > 0 && airCount > maxAirPerPage)
                {
                    builder.InsertBreak(BreakType.PageBreak);
                    airCount = 0;
                }

                airCount++;

                // 为每个酒店创建详细信息表格
                CreateAirDetailTable(builder, info);

                // 酒店之间添加间距，但不是最后一个
                if (i < airInfos.Count() - 1)
                {
                    AddSpacingBetweenHotels(builder);
                }

                // 检查是否达到每页最大酒店数量
                if (airCount >= maxAirPerPage && (i < airInfos.Count - 1 || i < airInfos.Count() - 1))
                {
                    builder.InsertBreak(BreakType.PageBreak);
                    airCount = 0;
                }
            }

            if (!string.IsNullOrEmpty(footerText))
            {
                AddFooter(builder, footerText);
            }
        }


        /// <summary>
        /// 生成酒店表格信息
        /// </summary>
        /// <param name="builder"></param>
        /// <param name="hotelInfos"></param>
        /// <param name="footerText"></param>
        public static void AddHotelInfoSections(DocumentBuilder builder, List<Grp_HotelReservations> hotelInfos, string? footerText)
        {
            if (hotelInfos == null || !hotelInfos.Any())
            {
                builder.Writeln("暂无酒店信息");
                return;
            }

            int hotelCount = 0;
            const int maxHotelsPerPage = 3; // 每页最多显示3个酒店，确保有足够空间

            for (int i = 0; i < hotelInfos.Count; i++)
            {
                var info = hotelInfos[i];

                // 检查是否需要分页
                if (hotelCount > 0 && hotelCount > maxHotelsPerPage)
                {
                    builder.InsertBreak(BreakType.PageBreak);
                    hotelCount = 0;
                }

                hotelCount++;

                AddCityHeader(builder, info.City);

                // 为每个酒店创建详细信息表格
                CreateHotelDetailTable(builder, info);

                // 酒店之间添加间距，但不是最后一个
                if (i < hotelInfos.Count() - 1)
                {
                    AddSpacingBetweenHotels(builder);
                }

                // 检查是否达到每页最大酒店数量
                if (hotelCount >= maxHotelsPerPage && (i < hotelInfos.Count - 1 || i < hotelInfos.Count() - 1))
                {
                    builder.InsertBreak(BreakType.PageBreak);
                    hotelCount = 0;
                }
            }

            if (!string.IsNullOrEmpty(footerText))
            {
                AddFooter(builder, footerText);
            }
        }

        private static void AddSpacingBetweenHotels(DocumentBuilder builder)
        {
            builder.Writeln();
            builder.InsertBreak(BreakType.LineBreak);
        }

        private static void AddCityHeader(DocumentBuilder builder, string cityName)
        {
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Left;
            builder.Font.Size = 14;
            builder.Font.Bold = true;
            builder.Font.Color = System.Drawing.Color.DarkGreen;
            builder.Writeln($"城市：{cityName}");

            builder.Font.Size = 10;
            builder.Font.Bold = false;
            builder.Font.Color = System.Drawing.Color.Black;
        }

        private static void CreateHotelDetailTable(DocumentBuilder builder, Grp_HotelReservations hotel)
        {
            // 设置表格不允许跨页断行，确保整个表格在一页上
            Table table = builder.StartTable();
            //table.AllowBreakAcrossPages = false; // 关键设置：不允许表格跨页

            // 设置表格样式
            ApplyTableStyle(table);

            // 添加酒店基本信息行
            AddTableRow(builder, "酒店名称", hotel.HotelName ?? "-", true);
            AddTableRow(builder, "联系电话", hotel.HotelTel ?? "-");
            AddTableRow(builder, "酒店地址", hotel.HotelAddress ?? "-");
            AddTableRow(builder, "确认号码", hotel.DetermineNo ?? "-");
            AddTableRow(builder, "入住日期", hotel.CheckInDate);
            AddTableRow(builder, "离店日期", hotel.CheckOutDate);
            AddTableRow(builder, "房间信息", hotel.RoomExplanation ?? "-");

            builder.EndTable();
        }

        private static void CreateAirDetailTable(DocumentBuilder builder, Grp_AirTicketReservations air)
        {
            // 设置表格不允许跨页断行，确保整个表格在一页上
            Table table = builder.StartTable();
            //table.AllowBreakAcrossPages = false; // 关键设置：不允许表格跨页

            // 设置表格样式
            ApplyTableStyle(table);

            // 添加酒店基本信息行
            AddTableRow(builder, "城市(A-B)", air.FlightsCity ?? "-", true);
            AddTableRow(builder, "航班日期", air.FlightsDate + " " + air.FlightsTime ?? "-");
            AddTableRow(builder, "机票票号", air.TicketNumber ?? "-");
            AddTableRow(builder, "航班简述", air.FlightsCode ?? "-");
            AddTableRow(builder, "航班描述", air.FlightsDescription);

            builder.EndTable();
        }


        private static void AddTableRow(DocumentBuilder builder, string title, string value, bool isHeaderRow = false)
        {
            // 左边单元格 - 标题
            builder.InsertCell();
            //builder.CellFormat.BackColor = isHeaderRow ? System.Drawing.Color.LightBlue : System.Drawing.Color.LightGray;
            builder.CellFormat.VerticalAlignment = CellVerticalAlignment.Center;
            builder.CellFormat.Width = 60;
            builder.Font.Bold = true;
            builder.Font.Size = isHeaderRow ? 12 : 10;
            builder.Font.Color = isHeaderRow ? System.Drawing.Color.DarkBlue : System.Drawing.Color.Black;
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
            builder.Write(title);

            // 右边单元格 - 值
            builder.InsertCell();
            //builder.CellFormat.BackColor = System.Drawing.Color.White;
            builder.CellFormat.VerticalAlignment = CellVerticalAlignment.Center;
            builder.CellFormat.Width = 200;
            builder.Font.Bold = isHeaderRow;
            builder.Font.Size = isHeaderRow ? 12 : 10;
            builder.Font.Color = System.Drawing.Color.Black;
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Left;
            builder.Write(value);

            builder.EndRow();

            // 重置单元格格式（除了宽度）
            //builder.CellFormat.BackColor = System.Drawing.Color.White;
            builder.Font.Bold = false;
            builder.Font.Size = 10;
            builder.Font.Color = System.Drawing.Color.Black;
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Left;
        }

        private static void ApplyTableStyle(Table table)
        {
            try
            {
                // 设置表格边框
                table.SetBorders(LineStyle.Single, 1.0, System.Drawing.Color.Gray);

                // 设置表格属性
                table.LeftPadding = 5;
                table.RightPadding = 5;
                table.TopPadding = 3;
                table.BottomPadding = 3;

                table.Alignment = TableAlignment.Left;
                table.AllowAutoFit = false;
                //table.AllowBreakAcrossPages = true;

                // 设置表格宽度
                table.PreferredWidth = PreferredWidth.FromPoints(400);

                // 设置列宽
                if (table.FirstRow != null && table.FirstRow.Cells.Count == 2)
                {
                    table.FirstRow.Cells[0].CellFormat.PreferredWidth = PreferredWidth.FromPercent(20);
                    table.FirstRow.Cells[1].CellFormat.PreferredWidth = PreferredWidth.FromPercent(80);
                }
            }
            catch (Exception ex)
            {
                //_logger.LogWarning(ex, "设置表格样式时发生错误");
            }
        }

        private static void AddFooter(DocumentBuilder builder, string footerText)
        {
            builder.InsertBreak(BreakType.ParagraphBreak);
            builder.Writeln();
            builder.Writeln();

            // 添加分隔线
            builder.ParagraphFormat.Alignment = ParagraphAlignment.Center;
            builder.Font.Size = 8;
            builder.Font.Color = System.Drawing.Color.LightGray;
            //builder.Writeln("────────────────────────────────────");

            builder.ParagraphFormat.Alignment = ParagraphAlignment.Right;
            builder.Font.Size = 9;
            builder.Font.Italic = true;
            builder.Font.Color = System.Drawing.Color.Gray;
            builder.Writeln(footerText);
            builder.Writeln($"文档生成时间：{DateTime.Now:yyyy-MM-dd HH:mm:ss}");

            builder.ParagraphFormat.Alignment = ParagraphAlignment.Left;
            builder.Font.Size = 10;
            builder.Font.Italic = false;
            builder.Font.Color = System.Drawing.Color.Black;
        }

    }
    #endregion

}