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


using Microsoft.AspNetCore.SignalR;
using Microsoft.International.Converters.PinYinConverter;
using Microsoft.VisualBasic;
using NPOI.HSSF.Util;
using OASystem.API.OAMethodLib.Hub.HubClients;
using OASystem.API.OAMethodLib.Hub.Hubs;
using OASystem.API.OAMethodLib.JuHeAPI;
using OASystem.API.OAMethodLib.SignalR.Hubs;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.District;
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.Infrastructure.Repositories.CRM;
using OASystem.Infrastructure.Repositories.Groups;
using System.Data;
using System.IdentityModel.Tokens.Jwt;
using System.IO.Compression;
using System.Security.Claims;
using System.Web;
using static OpenAI.GPT3.ObjectModels.SharedModels.IOpenAiModels;

namespace OASystem.API.OAMethodLib
{
    public static class GeneralMethod
    {
        //团组信息
        private readonly static DelegationInfoRepository _dirRep = AutofacIocManager.Instance.GetService<DelegationInfoRepository>();
        private readonly static TeamRateRepository _teamRateRep = AutofacIocManager.Instance.GetService<TeamRateRepository>();
        private readonly static IJuHeApiService _juHeApi = AutofacIocManager.Instance.GetService<IJuHeApiService>();
        private readonly static SetDataRepository _setDataRep = AutofacIocManager.Instance.GetService<SetDataRepository>();
        private readonly static TableOperationRecordRepository _tableOperationRecordRep = AutofacIocManager.Instance.GetService<TableOperationRecordRepository>();
        private readonly static MessageRepository _messageRep = AutofacIocManager.Instance.GetService<MessageRepository>();
        private readonly static IHubContext<ChatHub, IChatClient> _hubContext = AutofacIocManager.Instance.GetService<IHubContext<ChatHub, IChatClient>>();
        private readonly static string[] weekdays = new string[] { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };
        #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 (userId < 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>());
                        }
                    }
                }
            }
            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 团组任务进度通知
        /// </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)
            {
                //给在线在用户发送消息
                List<string> connIds = UserStore.OnlineUser.Where(it => userIds.Contains(it.UserId)).Select(it => it.ConnectionId).ToList();

                string notificationTypeStr1 = GetMsgNotificationType(msgTypeEnum);
                string 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="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 indentity = new ClaimsIdentity(claims, "formlogin");
            var principal = new ClaimsPrincipal(indentity);

            // 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;
            int _number = 0;
            bool 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="diId">团组Id</param>
        /// <param name="userId">用户Id</param>
        /// <param name="CTable">业务模块Id</param>
        /// <returns></returns>
        public static async Task<List<Web_ShareGroupInfoView>> PostOperationAuthReturnGroupInfosAsync(int userId, int CTable)
        {
            List<Web_ShareGroupInfoView> _ShareGroupInfoViews = new List<Web_ShareGroupInfoView>();
            var taskDatas = _dirRep._sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(it => it.UId == userId && it.CTId == CTable && it.IsDel == 0).ToList();

            if (taskDatas.Count < 1) return _ShareGroupInfoViews;

            string diIds = string.Join(",", taskDatas.Select(it => it.DIId).ToList());
            string 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 userDatas = 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)岗位
             */
            List<Grp_GroupsTaskAssignment> _GroupsTaskAssignments = new List<Grp_GroupsTaskAssignment>();
            // 76  酒店预订  --> 国交部门(7) 酒店(25) 岗位 
            _GroupsTaskAssignments.AddRange(
                userDatas
                .Where(it => it.DepId == 7 && it.JobPostId == 25)
                .Select(it => new Grp_GroupsTaskAssignment()
                {
                    DIId = diId,
                    CTId = 76,
                    UId = it.Id,
                    CreateUserId = userId
                }).ToList()
            );

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


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

            //80  签证  --> 国交部门(7) 签证(26) 岗位
            _GroupsTaskAssignments.AddRange(
                userDatas
                .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(
                userDatas
                .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) 岗位
            _GroupsTaskAssignments.AddRange(
                userDatas
                .Where(it => it.DepId == 7 && 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) 岗位
            List<int> depIds = new List<int>() { 7, 3, 5 };
            List<int> jobIds = new List<int>() { 1, 74 };
            _GroupsTaskAssignments.AddRange(
                userDatas
                .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()
            );

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

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

            //1015 超支费用  --> 国交部(ALL)岗位
            _GroupsTaskAssignments.AddRange(
              userDatas
              .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 = _teamRateRep._sqlSugar.Insertable(_GroupsTaskAssignments).ExecuteCommand();
            }

            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

        #endregion

        #region 团组汇率

        /// <summary>
        /// 团组汇率 
        /// 获取板块 币种 及 汇率
        /// 76	酒店预订  77	行程          79	车/导游地接
        /// 80	签证      82	团组客户保险  85	机票预订
        /// 98	其他款项  285	收款退还
        /// </summary>
        /// <param name="teamRateModels"></param>
        /// <param name="cTable"></param>
        /// <param name="currencyCode"></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="num"></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.Count(); 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.Count() < 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.Count(); 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(2023, 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>
        /// 根据星期,月份的缩写,转换成数字或者全称
        /// 根据币种中文名称返回币种代码
        /// 根据数字返回机型型号【2、3开头的就是空客，比如空客320，7开头的就是波音，比如波音777】
        /// 20210903贾文滔
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></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="temp"></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="diid">团组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="dt"></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



    }
}