OA2023/OASystem/OASystem.Infrastructure/Repositories/Groups/AirTicketResRepository.cs

using AutoMapper;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using OASystem.Domain;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Dtos.UserDto;
using OASystem.Domain.Entities.Business;
using OASystem.Domain.Entities.Customer;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Tools;
using Org.BouncyCastle.Asn1.Ocsp;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Xml.Linq;
using System.Net;
using NPOI.XWPF.UserModel;
using NPOI.OpenXmlFormats.Wordprocessing;
using System.Collections;
using Aspose.Words.Tables;
using Aspose.Words;
using System.Reflection;
using OASystem.Domain.Entities.Financial;
using NPOI.SS.Formula.Functions;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class AirTicketResRepository : BaseRepository<Grp_AirTicketReservations, Grp_AirTicketReservations>
    {
        private readonly IMapper _mapper;
        public AirTicketResRepository(SqlSugarClient sqlSugar, IMapper mapper) : base(sqlSugar)
        {
            _mapper = mapper;
        }

        public async Task<Result> AirTicketResById(AirTicketResByIdDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                Grp_AirTicketReservations grp_AirTicket = _sqlSugar.Queryable<Grp_AirTicketReservations>().First(a => a.Id == dto.Id && a.IsDel == 0);
                if (grp_AirTicket == null)
                {
                    return result = new Result() { Code = -1, Msg = "暂无数据" };
                }
                else
                {
                    Grp_CreditCardPayment grp_CreditCard = _sqlSugar.Queryable<Grp_CreditCardPayment>().First(a => a.CId == grp_AirTicket.Id && a.IsDel == 0 && a.CTable==85);
                    if (grp_CreditCard == null) { return result = new Result() { Code = -1, Msg = "暂无数据" }; }
                    return result = new Result()
                    {
                        Code = 0,
                        Msg = "查询成功！",
                        Data = new
                        {
                            AirTicket = grp_AirTicket,
                            CreditCard = grp_CreditCard,
                        }
                    };
                }
            }
            catch (Exception)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }

        public async Task<Result> AirTicketResList(AirTicketResDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };

            Grp_DelegationInfo _DelegationInfo = _sqlSugar.Queryable<Grp_DelegationInfo>().First(it => it.Id == dto.DiId);
            if (_DelegationInfo != null)
            {
                string UserId = "";
                List<Grp_GroupsTaskAssignment> gtaUIdList = _sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(a=>a.DIId==dto.DiId && a.IsDel==0 && a.CTId==85).ToList();
                foreach (Grp_GroupsTaskAssignment gta in gtaUIdList)
                    UserId += gta.UId + ",";

                if (!string.IsNullOrWhiteSpace(UserId))
                {
                    UserId = UserId.Substring(0, UserId.Length - 1);
                }
                else
                {
                    UserId = "0";
                }
                string sqlWhere = "";
                if (dto.IsPaySign != -1)
                {
                    sqlWhere += string.Format(@" And ccp.IsPay = {0} ", dto.IsPaySign);
                }

                string sql = string.Format(@"Select atr.*,ccp.IsAuditGM,sd1.Name As CTypeName,sd2.Name As PreCurrencyStr,sd3.Name As CurrencyStr,ccp.IsPay
                                             From Grp_AirTicketReservations atr
                                             Left Join Grp_CreditCardPayment ccp On atr.Id = ccp.CId  And atr.DIId = ccp.DIId And ccp.IsDel = 0
                                             Left Join Sys_SetData sd1 On sd1.Id = atr.ctype
                                             Left Join Sys_SetData sd2 On sd2.Id = atr.PreCurrency
                                             Left Join Sys_SetData sd3 On sd3.Id = atr.Currency
                                             Where atr.IsDel = 0 And atr.DIId={0} {1}
                                             Order By atr.FlightsDate Asc", dto.DiId, sqlWhere);
                List<AirTicketReservationsView> _AirTicketReservations = _sqlSugar.SqlQueryable<AirTicketReservationsView>(sql).ToList();

                foreach (var item in _AirTicketReservations)
                {
                    if (item.FlightsDescription.Contains("\n"))
                    {
                        var spilitArr = Regex.Split(item.FlightsDescription, "\n");
                        int rowindex = 1;
                        foreach (var spilitItem in spilitArr)
                        {
                            try
                            {
                                var spDotandEmpty = spilitItem.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
                                var depCode = spDotandEmpty[3].Substring(0, 3);
                                var arrCode = spDotandEmpty[3].Substring(3, 3);
                                Res_ThreeCode depData = _sqlSugar.Queryable<Res_ThreeCode>().First(it => it.IsDel == 0 && it.Three == depCode);
                                Res_ThreeCode arrData = _sqlSugar.Queryable<Res_ThreeCode>().First(it => it.IsDel == 0 && it.Three == arrCode);

                                string day = spDotandEmpty[2].Substring(2, 2);//日
                                string monthAbbreviations = spDotandEmpty[2].Substring(4, 3).ToUpper();//月份
                                switch (monthAbbreviations)
                                {
                                    case "JAN":
                                        monthAbbreviations = "1";
                                        break;
                                    case "FEB":
                                        monthAbbreviations = "2";
                                        break;
                                    case "MAR":
                                        monthAbbreviations = "3";
                                        break;
                                    case "APR":
                                        monthAbbreviations = "4";
                                        break;
                                    case "MAY":
                                        monthAbbreviations = "5";
                                        break;
                                    case "JUN":
                                        monthAbbreviations = "6";
                                        break;
                                    case "JUL":
                                        monthAbbreviations = "7";
                                        break;
                                    case "AUG":
                                        monthAbbreviations = "8";
                                        break;
                                    case "SEP":
                                        monthAbbreviations = "9";
                                        break;
                                    case "OCT":
                                        monthAbbreviations = "10";
                                        break;
                                    case "NOV":
                                        monthAbbreviations = "11";
                                        break;
                                    case "DEC":
                                        monthAbbreviations = "12";
                                        break;
                                }
                                string tate = $"{monthAbbreviations}月{day}日";
                                item.FlightDescription += rowindex + ". " + depData.AirPort + " " + arrData.AirPort + "  (" + tate + ")\r\n";
                            }
                            catch (Exception ex)
                            {
                                item.FlightDescription = "录入数据不规范！请检查";
                                break;
                            }
                            rowindex++;
                        }
                    }
                    else
                    {
                        try
                        {
                            var spDotandEmpty = item.FlightsDescription.Split('.')[1].Split(' ').Where(x => !string.IsNullOrEmpty(x)).ToList();
                            var depCode = spDotandEmpty[3].Substring(0, 3);
                            var arrCode = spDotandEmpty[3].Substring(3, 3);
                            Res_ThreeCode depData = _sqlSugar.Queryable<Res_ThreeCode>().First(it => it.IsDel == 0 && it.Three == depCode);
                            Res_ThreeCode arrData = _sqlSugar.Queryable<Res_ThreeCode>().First(it => it.IsDel == 0 && it.Three == arrCode);
                            string day = spDotandEmpty[2].Substring(2, 2);//日
                            string monthAbbreviations = spDotandEmpty[2].Substring(4, 3).ToUpper();//月份
                            switch (monthAbbreviations)
                            {
                                case "JAN":
                                    monthAbbreviations = "1";
                                    break;
                                case "FEB":
                                    monthAbbreviations = "2";
                                    break;
                                case "MAR":
                                    monthAbbreviations = "3";
                                    break;
                                case "APR":
                                    monthAbbreviations = "4";
                                    break;
                                case "MAY":
                                    monthAbbreviations = "5";
                                    break;
                                case "JUN":
                                    monthAbbreviations = "6";
                                    break;
                                case "JUL":
                                    monthAbbreviations = "7";
                                    break;
                                case "AUG":
                                    monthAbbreviations = "8";
                                    break;
                                case "SEP":
                                    monthAbbreviations = "9";
                                    break;
                                case "OCT":
                                    monthAbbreviations = "10";
                                    break;
                                case "NOV":
                                    monthAbbreviations = "11";
                                    break;
                                case "DEC":
                                    monthAbbreviations = "12";
                                    break;
                            }
                            string tate = $"{monthAbbreviations}月{day}日";
                            item.FlightDescription += depData.AirPort + " " + arrData.AirPort + "  (" + tate + ")\r\n";
                        }
                        catch (Exception)
                        {
                            item.FlightDescription = "录入数据不规范！请检查";
                        }
                    }
                }
                //团组成本预算表查询
                Grp_GroupCostParameter _GroupCostParameter = _sqlSugar.Queryable<Grp_GroupCostParameter>().First(a => a.DiId == dto.DiId && a.IsDel==0 && a.CostType =="A" && a.IsShare == 1);

                AirGroupCostParameterView _AirgroupCostParameter = _mapper.Map<AirGroupCostParameterView>(_GroupCostParameter);
                for (int i = 0; i <_AirTicketReservations.Count; i++)
                {
                  string [] ClientArr=  _AirTicketReservations[i].ClientName.Split(',').Where(x=>!string.IsNullOrWhiteSpace(x)).ToArray();
                    


                    foreach (var item in ClientArr)
                    {
                        bool isNumeric = int.TryParse(item, out _);

                        if (isNumeric)
                        {
                            if (item == "-1")
                            {
                                _AirTicketReservations[i].ClientNameStr += "行程单,";
                            }
                            else
                            {
                                Crm_DeleClient DeleClient = _sqlSugar.Queryable<Crm_DeleClient>().Where(a => a.IsDel == 0 && a.Id == int.Parse(item)).First();
                                _AirTicketReservations[i].ClientNameStr += DeleClient.Pinyin + ',';
                            }
                        }
                        else
                        {
                            _AirTicketReservations[i].ClientNameStr += item;
                        }

                        
                    }
                    if (_AirTicketReservations[i].ClientNameStr is not null)
                    {
                        _AirTicketReservations[i].ClientNameStr = _AirTicketReservations[i].ClientNameStr.Substring(0, _AirTicketReservations[i].ClientNameStr.Length - 1);
                    }
                    
                }

                if (dto.PortType==1)
                {
                    var data = new
                    {
                        DelegationInfo = _DelegationInfo,
                        AirTicketReservations = _AirTicketReservations,
                        AirGroupCostParameter = _AirgroupCostParameter
                    };
                    return result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
                }
                else if(dto.PortType == 2 || dto.PortType==3)
                {
                    int count = _AirTicketReservations.Count;
                    float totalPage = (float)count / dto.PageSize;//总页数
                    if (totalPage == 0) totalPage = 1;
                    else totalPage = (int)Math.Ceiling((double)totalPage);

                    List<AirTicketReservationsView> grp_AirTickets = new List<AirTicketReservationsView>();
                    for (int i = 0; i < dto.PageSize; i++)
                    {
                        var RowIndex = i + (dto.PageIndex - 1) * dto.PageSize;
                        if (RowIndex < _AirTicketReservations.Count)
                        {
                            grp_AirTickets.Add(_AirTicketReservations[RowIndex]);
                        }
                        else
                        {
                            break;
                        }
                    }
                    ListViewBase<AirTicketReservationsView> rst = new ListViewBase<AirTicketReservationsView>();
                    rst.DataList = grp_AirTickets;
                    rst.DataCount = count;
                    rst.CurrPageIndex = dto.PageIndex;
                    rst.CurrPageSize = dto.PageSize;
                    var data = new
                    {
                        AirData= rst,
                        AirGroupCostParameter = _AirgroupCostParameter
                    };
                    return result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
                }
                else
                {
                    return result = new Result() { Code = -1, Msg = "请传入PortType参数！1:Web，2:Android,3:IOS！" };
                }
            }
            else
            {
                return result = new Result() { Code = -1, Msg = "暂无团组数据！" };
            }

        }

        public async Task<Result> AirTicketResSelect(AirTicketResDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                if (dto.DiId < 1)
                {
                    dto.DiId = -1;
                }

                #region 团组下拉框


                List<Grp_GroupsTaskAssignment> grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == 85).ToList();
                List<GroupNameView> grp_NameView = new List<GroupNameView>();
                string DiId = "";
                foreach (var item in grp_GroupsTaskAssignment)
                {
                    DiId += item.DIId + ",";
                }
                if (DiId.Length>1)
                {
                    DiId = DiId.Substring(0, DiId.Length - 1);
                
                string sql = string.Format(@"select * from Grp_DelegationInfo where Id in({0}) and IsDel={1} Order By Id Desc", DiId, 0);
                List<Grp_DelegationInfo> grp_Delegations = _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).ToList();
                    
                    if (grp_Delegations.Count == 0)
                {
                    return result = new Result() { Code = -1, Msg = "查询失败！" };
                }
                
                foreach (var item in grp_Delegations)
                {
                    GroupNameView groupNameView = new GroupNameView();
                    groupNameView.Id = item.Id;
                    groupNameView.GroupName = item.TeamName;
                    grp_NameView.Add(groupNameView);
                }
                }
                #endregion

                #region 其他下拉框查询
                //舱位类型
                List<Sys_SetData> TicketClass = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 44 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _TicketClassa = _mapper.Map<List<SetDataInfoView>>(TicketClass);
                //支付方式
                List<Sys_SetData> Payment = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 14 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _Payment = _mapper.Map<List<SetDataInfoView>>(Payment);
                //卡类型
                List<Sys_SetData> CardType = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 15 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _CardType = _mapper.Map<List<SetDataInfoView>>(CardType);
                //合作方资料
                List<Res_AirTicketAgent> _AirTicketAgents = _sqlSugar.Queryable<Res_AirTicketAgent>().Where(a => a.IsDel == 0).ToList();

                #endregion

                #region 客人名单下拉框
                if (dto.DiId == -1)
                {
                    var dele = _AirTicketAgents.FirstOrDefault();
                    dto.DiId = dele != null ? dele.Id : dto.DiId;
                }
                string sqlClient = string.Format("select b.Id,b.Pinyin,b.lastName,b.firstName,b.phone from  Grp_TourClientList a, Crm_DeleClient b where a.clientid = b.id and a.isdel = 0 and a.diid = {0}", dto.DiId);
                var clientArr = _sqlSugar.SqlQueryable<SimplClientInfo>(sqlClient).ToList();
                #endregion

                var data = new
                {
                    TicketClass = _TicketClassa,
                    Payment = _Payment,
                    CardType = _CardType,
                    GroupName = grp_NameView,
                    AirTicketAgents = _AirTicketAgents,
                    clientArr
                };

                return result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "程序错误" };
            }
        }

        public async Task<Result> OpAirTicketRes(AirTicketResOpDto dto,Func<int,int,int,Task<Result>> fn)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                BeginTran();
                int id = 0;
                Grp_AirTicketReservations grp_AirTicket = _mapper.Map<Grp_AirTicketReservations>(dto.AirTicketResOpData);
                if (dto.Status == 1)
                {
                    //string selectSql = string.Format(@"select * from Grp_AirTicketReservations where CONVERT(nvarchar,ClientName)= '{0}' and IsDel={1} and FlightsCode='{2}'"
                    //                                 , dto.AirTicketResOpData.ClientName, 0,dto.AirTicketResOpData.FlightsCode);
                    //var DeleClient = await _sqlSugar.SqlQueryable<Grp_AirTicketReservations>(selectSql).FirstAsync();//查询是否存在
                    //if (DeleClient != null)
                    //{
                    //    return result = new Result() { Code = -1, Msg = "该机票信息已存在，请勿重复添加！" };
                    //}
                    //else//不存在，可添加
                    //{
                        //grp_AirTicket.ArrivedTime = "";
                        //grp_AirTicket.LeaveDescription = "";
                        //grp_AirTicket.FlightsDescription = "";
                        //grp_AirTicket.DeleteUserId = 0;
                        //grp_AirTicket.DeleteTime = "";
                        //grp_AirTicket.ReturnDescription = "";
                        grp_AirTicket.FlightsDate= DateTime.Parse(grp_AirTicket.FlightsDate).ToString("yyyy-MM-dd");
                        id = await AddAsyncReturnId(grp_AirTicket);
                        if (id == 0)
                        {
                            result = new Result() { Code = -1, Msg = "添加失败！" };

                        }
                        else
                        {
                            result = new Result() { Code = 0, Msg = "添加成功！" };
                        }

                   // }
                    if (result.Code == 0)
                    {
                        Grp_CreditCardPayment grp_CreditCard = _mapper.Map<Grp_CreditCardPayment>(dto.CardPaymentOpData);

                        //PayDId == 刷卡 IsPay == 1
                        //if (dto.CardPaymentOpData.PayDId == 72)
                        //{
                        //    grp_CreditCard.IsPay = 1;
                        //}
                        //else grp_CreditCard.IsPay = 0;

                        grp_CreditCard.IsPay = 0;

                        //获取新汇率  int diId,int CId, int currencyId
                        var rate = await fn(dto.AirTicketResOpData.DiId,85,dto.AirTicketResOpData.Currency);

                        if (rate.Code == 0)
                        {
                            var rateInfo =  (rate.Data as CurrencyInfo);
                            if (rateInfo is not null)
                            {

                                grp_CreditCard.DayRate = rateInfo.Rate;
                                grp_CreditCard.RMBPrice = rateInfo.Rate * grp_CreditCard.PayMoney;
                            }
                            else
                            {
                                grp_CreditCard.DayRate = 1;
                                grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                            }
                        }

                        //换算
                        #region 老汇率处理方式
                        //Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == grp_AirTicket.DIId && a.IsDel == 0 && a.CTable == 85);
                        //if (_TeamRate != null)
                        //{
                        //    if (grp_CreditCard.PaymentCurrency == 49)
                        //    {
                        //        grp_CreditCard.DayRate = _TeamRate.RateU;
                        //        grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney * Convert.ToDecimal(_TeamRate.RateU);
                        //        //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateU); 
                        //    }
                        //    else if (grp_CreditCard.PaymentCurrency == 51)
                        //    {
                        //        grp_CreditCard.DayRate = _TeamRate.RateE;
                        //        grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney * Convert.ToDecimal(_TeamRate.RateE);
                        //        //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateE); 
                        //    }
                        //    else
                        //    {
                        //        grp_CreditCard.DayRate = 1M;
                        //        grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                        //    }
                        //}
                        //else
                        //{
                        //    grp_CreditCard.DayRate = 1M;
                        //    grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                        //}
                        #endregion


                        //判断是否超出成本
                        #region 自动审核(暂时屏蔽)
                        //Grp_GroupCostParameter _GroupCostParameter = _sqlSugar.Queryable<Grp_GroupCostParameter>().First(a => a.DiId == grp_AirTicket.DIId && a.IsDel == 0);
                        //if (grp_AirTicket.CType == 460)//经济舱
                        //{
                        //    if (_GroupCostParameter != null)
                        //    {
                               
                        //        if (Convert.ToDecimal(_GroupCostParameter.JJCCB) * Convert.ToDecimal(grp_AirTicket.ClientNum) > grp_CreditCard.RMBPrice)
                        //        {
                        //            grp_CreditCard.ExceedBudget = 0;// 超出预算比例 换算
                        //            grp_CreditCard.IsAuditGM = 3;//3 为自动审核
                        //            grp_CreditCard.AuditGMOperate = 0;
                        //            grp_CreditCard.AuditGMDate = "";
                        //            grp_CreditCard.IsPay = 0;
                        //        }
                        //        else
                        //        {
                        //            var a = grp_CreditCard.RMBPrice - Convert.ToDecimal(_GroupCostParameter.JJCCB);
                        //            var b = a / Convert.ToDecimal(_GroupCostParameter.JJCCB) * Convert.ToDecimal(grp_AirTicket.ClientNum);
                        //            grp_CreditCard.ExceedBudget = b;// 超出预算比例 换算
                        //            grp_CreditCard.IsAuditGM = 0;//3 为自动审核
                        //            grp_CreditCard.AuditGMOperate = 21;
                        //            grp_CreditCard.AuditGMDate = "";
                        //            grp_CreditCard.IsPay = 0;
                        //        }
                               
                        //    }
                        //    else
                        //    {
                        //        grp_CreditCard.ExceedBudget = 0.00M;// 超出预算比例 换算
                        //        grp_CreditCard.IsAuditGM = 0;//3 为自动审核
                        //        grp_CreditCard.AuditGMOperate = 0;
                        //        grp_CreditCard.AuditGMDate = "";
                        //        grp_CreditCard.IsPay = 0;
                        //    }

                        //}
                        //else if (grp_AirTicket.CType == 458)
                        //{
                        //    if (_GroupCostParameter != null)
                        //    {


                        //        if (Convert.ToDecimal(_GroupCostParameter.GWCB) * Convert.ToDecimal(grp_AirTicket.ClientNum) > grp_CreditCard.RMBPrice)
                        //        {
                        //            grp_CreditCard.ExceedBudget = 0;// 超出预算比例 换算
                        //            grp_CreditCard.IsAuditGM = 3;//3 为自动审核
                        //            grp_CreditCard.AuditGMOperate = 21;
                        //            grp_CreditCard.AuditGMDate = "";
                        //            grp_CreditCard.IsPay = 0;
                        //        }
                        //        else
                        //        {
                        //            var a = grp_CreditCard.RMBPrice - Convert.ToDecimal(_GroupCostParameter.GWCB);
                        //            var b = a / Convert.ToDecimal(_GroupCostParameter.GWCB) * Convert.ToDecimal(grp_AirTicket.ClientNum);
                        //            grp_CreditCard.ExceedBudget = b;// 超出预算比例 换算
                        //            grp_CreditCard.IsAuditGM = 0;//3 为自动审核
                        //            grp_CreditCard.AuditGMOperate = 21;
                        //            grp_CreditCard.AuditGMDate = "";
                        //            grp_CreditCard.IsPay = 0;
                        //        }
                        //    }
                        //    else
                        //    {
                        //        grp_CreditCard.ExceedBudget = 0.00M;// 超出预算比例 换算
                        //        grp_CreditCard.IsAuditGM = 0;//3 为自动审核
                        //        grp_CreditCard.AuditGMOperate = 21;
                        //        grp_CreditCard.AuditGMDate = "";
                        //        grp_CreditCard.IsPay = 0;
                        //    }
                        //}
                        //else
                        //{
                        //    grp_CreditCard.ExceedBudget = 0.00M;// 超出预算比例 换算
                        //    grp_CreditCard.IsAuditGM = 0;//3 为自动审核
                        //    grp_CreditCard.AuditGMOperate = 0;
                        //    grp_CreditCard.AuditGMDate = "";
                        //    grp_CreditCard.IsPay = 0;
                        //}
                        #endregion

                        grp_CreditCard.CId = id;
                        grp_CreditCard.CTable = 85;
                        grp_CreditCard.PayPercentage = 100;

                        #region 查询上一次付款(注释)
                        ////查询上一次付款信息
                        //Grp_CreditCardPayment _CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().OrderByDescending(x => x.CreateUserId).First(a => a.DIId == grp_AirTicket.DIId && a.IsDel == 0 && a.IsPay == 1);
                        //if (_CreditCardPayment != null)
                        //{
                        //    grp_CreditCard.PayPercentageOld = _CreditCardPayment.PayPercentageOld;// 上次付款百分比 查询并计算最近一次
                        //    grp_CreditCard.PayThenMoneyOld = _CreditCardPayment.PayMoney;// 上次付款金额 查询上一次
                        //    grp_CreditCard.UpdateDate = _CreditCardPayment.UpdateDate;// 上次付款时间 查询上一次
                        //}
                        //else
                        //{
                        //    grp_CreditCard.PayPercentageOld = 0;// 上次付款百分比 查询并计算最近一次
                        //    grp_CreditCard.PayThenMoneyOld = 0;// 上次付款金额 查询上一次
                        //    grp_CreditCard.UpdateDate = "";// 上次付款时间 查询上一次
                        //}
                        #endregion

                        id = await _sqlSugar.Insertable(grp_CreditCard).ExecuteReturnIdentityAsync();
                        if (id == 0)
                        {
                            RollbackTran();
                            return result = new Result() { Code = -1, Msg = "添加失败！" };

                        }
                        await UpdateAsync(a => a.Id == id, a => new Grp_AirTicketReservations
                        {
                            FlightsDescription = dto.AirTicketResOpData.FlightsDescription,
                        });
                        CommitTran();

                        var data = new { ccpId = id, sign = 1 };
                        return result = new Result() { Code = 0, Msg = "添加成功！", Data = data };
                    }
                    else
                    {
                        RollbackTran();
                        return result = new Result() { Code = -1, Msg = "添加失败！" };

                    }
                }
                else if (dto.Status == 2)
                {
                    id = dto.AirTicketResOpData.Id;

                    bool res = await UpdateAsync(a => a.Id == dto.AirTicketResOpData.Id, a => new Grp_AirTicketReservations
                    {
                        CType = dto.AirTicketResOpData.CType,
                        PrePrice = dto.AirTicketResOpData.PrePrice,
                        PreCurrency = dto.AirTicketResOpData.PreCurrency,
                        Price = dto.AirTicketResOpData.Price,
                        Currency = dto.AirTicketResOpData.Currency,
                        ClientNum = dto.AirTicketResOpData.ClientNum,
                        ClientName = dto.AirTicketResOpData.ClientName,
                        IsCheckIn = dto.AirTicketResOpData.IsCheckIn,
                        IsSetSeat = dto.AirTicketResOpData.IsSetSeat,
                        IsPackage = dto.AirTicketResOpData.IsPackage,
                        IsBagHandle = dto.AirTicketResOpData.IsBagHandle,
                        IsTrain = dto.AirTicketResOpData.IsTrain,
                        FlightsCode = dto.AirTicketResOpData.FlightsCode,
                        FlightsCity = dto.AirTicketResOpData.FlightsCity,
                        FlightsDescription = dto.AirTicketResOpData.FlightsDescription,
                        PriceDescription = dto.AirTicketResOpData.PriceDescription,
                        TicketNumber = dto.AirTicketResOpData.TicketNumber,
                        TicketCode = dto.AirTicketResOpData.TicketCode,
                        Remark = dto.AirTicketResOpData.Remark,
                    });
                    if (!res){result = new Result() { Code = -1, Msg = "修改失败！" }; }
                    else { result = new Result() { Code = 0, Msg = "修改成功！" }; }

                    if (result.Code == 0)
                    {
                        //C表操作
                        Grp_CreditCardPayment grp_CreditCard = _mapper.Map<Grp_CreditCardPayment>(dto.CardPaymentOpData);

                        //PayDId == 刷卡 IsPay == 1
                        //if (grp_CreditCard.PayDId == 72) grp_CreditCard.IsPay = 1;
                        //else grp_CreditCard.IsPay = 0;
                        grp_CreditCard.IsPay = 0;

                        //获取新汇率  int diId,int CId, int currencyId
                        var rate = await fn(dto.AirTicketResOpData.DiId, 85, dto.AirTicketResOpData.Currency);

                        if (rate.Code == 0)
                        {
                            var rateInfo = (rate.Data as CurrencyInfo);
                            if (rateInfo is not null)
                            {

                                grp_CreditCard.DayRate = rateInfo.Rate;
                                grp_CreditCard.RMBPrice = rateInfo.Rate * grp_CreditCard.PayMoney;
                            }
                            else
                            {
                                grp_CreditCard.DayRate = 1;
                                grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                            }
                        }

                        #region 老汇率修改(注释)
                        //换算
                        Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == grp_AirTicket.DIId && a.IsDel == 0 && a.CTable == 85);
                        //if (_TeamRate != null)
                        //{
                        //    if (grp_CreditCard.PaymentCurrency == 49)
                        //    {
                        //        grp_CreditCard.DayRate = _TeamRate.RateU;
                        //        grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney * Convert.ToDecimal(_TeamRate.RateU);
                        //        //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateU); 
                        //    }
                        //    else if (grp_CreditCard.PaymentCurrency == 51)
                        //    {
                        //        grp_CreditCard.DayRate = _TeamRate.RateE;
                        //        grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney * Convert.ToDecimal(_TeamRate.RateE);
                        //        //ccp.PayMoney = ccp.PayMoney * float.Parse(tr.RateE); 
                        //    }
                        //    else
                        //    {
                        //        grp_CreditCard.DayRate = 1M;
                        //        grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                        //    }
                        //}
                        //else
                        //{
                        //    grp_CreditCard.DayRate = 1M;
                        //    grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                        //}
                        #endregion

                        #region 自动审核(注释)
                        //Grp_GroupCostParameter _GroupCostParameter = _sqlSugar.Queryable<Grp_GroupCostParameter>().First(a => a.DiId == grp_AirTicket.DIId && a.IsDel == 0);
                        //if (grp_AirTicket.CType == 460)//经济舱
                        //{
                        //    if (_GroupCostParameter != null)
                        //    {


                        //        if (Convert.ToDecimal(_GroupCostParameter.JJCCB) * Convert.ToDecimal(grp_AirTicket.ClientNum) > grp_CreditCard.RMBPrice)
                        //        {
                        //            grp_CreditCard.ExceedBudget = 0;// 超出预算比例 换算
                        //            grp_CreditCard.IsAuditGM = 3;//3 为自动审核
                        //            grp_CreditCard.AuditGMOperate = 0;
                        //            grp_CreditCard.AuditGMDate = "";
                        //            grp_CreditCard.IsPay = 1;
                        //        }
                        //        else
                        //        {
                        //            var a = grp_CreditCard.RMBPrice - Convert.ToDecimal(_GroupCostParameter.JJCCB);
                        //            var b = a / Convert.ToDecimal(_GroupCostParameter.JJCCB) * Convert.ToDecimal(grp_AirTicket.ClientNum);
                        //            grp_CreditCard.ExceedBudget = b;// 超出预算比例 换算
                        //            grp_CreditCard.IsAuditGM = 0;//3 为自动审核
                        //            grp_CreditCard.AuditGMOperate = 21;
                        //            grp_CreditCard.AuditGMDate = "";
                        //            grp_CreditCard.IsPay = 1;
                        //        }
                        //    }
                        //    else
                        //    {
                        //        grp_CreditCard.ExceedBudget = 0.00M;// 超出预算比例 换算
                        //        grp_CreditCard.IsAuditGM = 0;//3 为自动审核
                        //        grp_CreditCard.AuditGMOperate = 21;
                        //        grp_CreditCard.AuditGMDate = "";
                        //        grp_CreditCard.IsPay = 0;
                        //    }
                        //}
                        //else if (grp_AirTicket.CType == 458)
                        //{
                        //    if (_GroupCostParameter != null)
                        //    {
                        //        if ((_GroupCostParameter != null ? _GroupCostParameter.GWCB : 0) * Convert.ToDecimal(grp_AirTicket.ClientNum) > grp_CreditCard.RMBPrice)
                        //        {
                        //            grp_CreditCard.ExceedBudget = 0;// 超出预算比例 换算
                        //            grp_CreditCard.IsAuditGM = 3;//3 为自动审核
                        //            grp_CreditCard.AuditGMOperate = 21;
                        //            grp_CreditCard.AuditGMDate = "";
                        //            grp_CreditCard.IsPay = 1;
                        //        }
                        //        else
                        //        {
                        //            var a = grp_CreditCard.RMBPrice - Convert.ToDecimal(_GroupCostParameter.GWCB);
                        //            var b = a / Convert.ToDecimal(_GroupCostParameter.GWCB) * Convert.ToDecimal(grp_AirTicket.ClientNum);
                        //            grp_CreditCard.ExceedBudget = b;// 超出预算比例 换算
                        //            grp_CreditCard.IsAuditGM = 0;//3 为自动审核
                        //            grp_CreditCard.AuditGMOperate = 21;
                        //            grp_CreditCard.AuditGMDate = "";
                        //            grp_CreditCard.IsPay = 1;
                        //        }
                        //    }
                        //    else
                        //    {
                        //        grp_CreditCard.ExceedBudget = 0.00M;// 超出预算比例 换算
                        //        grp_CreditCard.IsAuditGM = 0;//3 为自动审核
                        //        grp_CreditCard.AuditGMOperate = 21;
                        //        grp_CreditCard.AuditGMDate = "";
                        //        grp_CreditCard.IsPay = 0;
                        //    }
                        //}
                        #endregion

                        grp_CreditCard.CId = id;
                        grp_CreditCard.CTable = 85;
                        grp_CreditCard.PayPercentage = 100;

                        #region 查询上一次付款信息(注释)
                        ////查询上一次付款信息
                        //Grp_CreditCardPayment _CreditCardPayment = _sqlSugar.Queryable<Grp_CreditCardPayment>().OrderByDescending(x => x.CreateUserId).First(a => a.DIId == grp_AirTicket.DIId && a.IsDel == 0 && a.IsPay == 1);
                        //if (_CreditCardPayment != null)
                        //{
                        //    grp_CreditCard.PayPercentageOld = _CreditCardPayment.PayPercentageOld;// 上次付款百分比 查询并计算最近一次
                        //    grp_CreditCard.PayThenMoneyOld = _CreditCardPayment.PayMoney;// 上次付款金额 查询上一次
                        //    grp_CreditCard.UpdateDate = _CreditCardPayment.UpdateDate;// 上次付款时间 查询上一次
                        //}
                        //else
                        //{
                        //    grp_CreditCard.PayPercentageOld = 0;// 上次付款百分比 查询并计算最近一次
                        //    grp_CreditCard.PayThenMoneyOld = 0;// 上次付款金额 查询上一次
                        //    grp_CreditCard.UpdateDate = "";// 上次付款时间 查询上一次
                        //}
                        #endregion

                        int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.Id == dto.CardPaymentOpData.Id).SetColumns(a => new Grp_CreditCardPayment
                        {
                            PayDId = grp_CreditCard.PayDId,
                            ConsumptionPatterns = grp_CreditCard.ConsumptionPatterns,
                            ConsumptionDate = grp_CreditCard.ConsumptionDate,
                            CTDId = grp_CreditCard.CTDId,
                            BankNo = grp_CreditCard.BankNo,
                            CardholderName = grp_CreditCard.CardholderName,
                            PayMoney = grp_CreditCard.PayMoney,
                            PaymentCurrency = grp_CreditCard.PaymentCurrency,
                            DayRate = grp_CreditCard.DayRate,
                            CompanyBankNo = grp_CreditCard.CompanyBankNo,
                            OtherBankName = grp_CreditCard.OtherBankName,
                            OtherSideNo = grp_CreditCard.OtherSideNo,
                            OtherSideName = grp_CreditCard.OtherSideName,
                            IsAuditGM = grp_CreditCard.IsAuditGM,
                            AuditGMOperate = grp_CreditCard.AuditGMOperate,
                            AuditGMDate = grp_CreditCard.AuditGMDate,
                            IsPay = grp_CreditCard.IsPay,
                            PayPercentage = grp_CreditCard.PayPercentage,
                            PayThenMoney = grp_CreditCard.PayThenMoney,
                            PayPercentageOld = grp_CreditCard.PayPercentageOld,
                            PayThenMoneyOld = grp_CreditCard.PayThenMoneyOld,
                            UpdateDate = grp_CreditCard.UpdateDate,
                            Payee = grp_CreditCard.Payee,
                            RMBPrice = grp_CreditCard.RMBPrice,
                            OrbitalPrivateTransfer = grp_CreditCard.OrbitalPrivateTransfer,
                            ExceedBudget = grp_CreditCard.ExceedBudget,
                            Remark= grp_CreditCard.Remark,
                        }).ExecuteCommandAsync();
                        if (CTable == 0)
                        {
                            RollbackTran();
                            return result = new Result() { Code = -1, Msg = "修改失败！" };
                        }
                        else
                        {
                            Grp_CreditCardPayment ccp = Query<Grp_CreditCardPayment>(a => a.Id == dto.CardPaymentOpData.Id).First();

                            var data = new { ccpId = ccp.Id, sign = 2 };
                            result = new Result() { Code = 0, Msg = "修改成功！", Data = data };
                        }
                    }
                    else
                    {
                        result = new Result() { Code = -1, Msg = "修改失败！" };
                        RollbackTran();
                    }
                }
                CommitTran();
            }
            catch (Exception ex)
            {
                RollbackTran();
                return result = new Result() { Code = -2, Msg = "程序错误" };
                throw;
            }
            return result;
        }

        /// <summary>
        /// 导出机票费用报表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<Result> DeriveAirTicketRes(AirTicketResDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                string sql = string.Format(@"Select atr.*,ccp.IsAuditGM,sd1.Name As 'CTypeName',sd2.Name As 'PreCurrencyStr',sd3.Name  As CurrencyStr
                                             From Grp_AirTicketReservations atr
                                             Left Join Grp_CreditCardPayment ccp On atr.Id = ccp.CId  And atr.DIId = ccp.DIId And ccp.IsDel = 0
                                             Left Join Sys_SetData sd1 On sd1.Id = atr.ctype
                                             Left Join Sys_SetData sd2 On sd2.Id = atr.PreCurrency
                                             Left Join Sys_SetData sd3 On sd3.Id = atr.Currency
                                             Where atr.IsDel = 0 And atr.DIId={0}
                                             Order By atr.FlightsDate Asc", dto.DiId);
                List<AirTicketReservationsPayView> _AirTicketReservations = _sqlSugar.SqlQueryable<AirTicketReservationsPayView>(sql).ToList();
                if (_AirTicketReservations.Count != 0)
                {
                    Grp_DelegationInfo grp_Delegation = _sqlSugar.Queryable<Grp_DelegationInfo>().First(a => a.Id == dto.DiId && a.IsDel == 0);
                    Sys_Users _Users = _sqlSugar.Queryable<Sys_Users>().First(a => a.Id == dto.UserId && a.IsDel == 0);
                    return result = new Result() { Code = 0, Msg = "成功", Data = new { Delegation = grp_Delegation, AirTicketRes = _AirTicketReservations, Users = _Users } };
                }
                else
                {
                    return result = new Result() { Code = -1, Msg = "暂无数据", Data = null };
                }
            }
            catch (Exception)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
            }
        }

        public async Task<Result> ItineraryAirTicketRes(ItineraryAirTicketResDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                string sql = string.Format(@"select a.*,c.IsAuditGM,(select Name from Sys_SetData where Id=a.cType) as 'CTypeName',(select Name from 
                                                Sys_SetData where Id=a.PreCurrency) as 'PreCurrencyStr',(select Name from Sys_SetData where Id=a.Currency)
                                                as 'CurrencyStr' from Grp_AirTicketReservations a,Grp_CreditCardPayment c where  a.id=c.CId  and a.isdel={1}
                                                and a.DIId={0} Order By a.CreateTime desc", dto.DiId, 0);
                List<AirTicketReservationsView> _AirTicketReservations = _sqlSugar.SqlQueryable<AirTicketReservationsView>(sql).ToList();
                if (_AirTicketReservations.Count == 0)
                {
                    return result = new Result() { Code = -1, Msg = "暂无数据", Data = null };
                }
                else
                {
                    return result = new Result() { Code = 0, Msg = "查询成功", Data = _AirTicketReservations };
                }
            }
            catch (Exception)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }

        public List<TranslateResult> ReTransBatch(List<string> list, string aimlanguage)
        {

            List<TranslateResult> reultStr = new List<TranslateResult>();
            if (list.Count < 0)
            {
                return reultStr;
            }
            //待翻译内容,必须是UTF-8编码
            string q = "";
            string Qtext = "";
            foreach (var item in list)
            {
                q += item;

                Qtext += "q=" + item + "&";
            }

            if (Qtext.Length > 0)
            {
                Qtext = Qtext.Substring(0, Qtext.Length - 1);
            }

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

            string url = "https://openapi.youdao.com/v2/api";

            //应用ID
            string appKey = "0fe3bc01e109ed36";
            //应用应用密钥
            string appSecret = "1f2x9TrqJfSBEJ8iH9GEFGgTyaYGjEry";
            //UUID
            string salt = DateTime.Now.Millisecond.ToString();
            //源语言
            dic.Add("from", "zh-CHS");
            //目标语言
            dic.Add("to", aimlanguage);
            //签名类型
            dic.Add("signType", "v3");
            //时间戳
            string curtime = ((GetBeijingTime().ToUniversalTime().Ticks - 621355968000000000) / 10000000).ToString();
            dic.Add("curtime", curtime);
            string signStr = appKey + Truncate(q) + salt + curtime + appSecret; ;
            string sign = ComputeHash(signStr, new SHA256CryptoServiceProvider());


            dic.Add("q", Qtext);
            dic.Add("appKey", appKey);
            dic.Add("salt", salt);
            dic.Add("sign", sign);
            string jsonStr = "";

            try
            {
                //Thread.Sleep(500);
                jsonStr = TransSync(url, dic);

                JObject trans = (JObject)JsonConvert.DeserializeObject(jsonStr);
                string errorCode = trans["errorCode"].ToString();
                if (errorCode == "0")
                {
                    reultStr = JsonConvert.DeserializeObject<List<TranslateResult>>(trans["translateResults"].ToString());
                }

                return reultStr;
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                return reultStr;
            }
        }
        public string Processing(string str)//处理这段英文的方法
        {

            if (string.IsNullOrEmpty(str)) { return ""; }

            string[] strArray = new string[] { };

            if (str.Contains(" ")) strArray = str.Split(" ".ToCharArray());
            else if (str.Contains(",")) strArray = str.Split(",".ToCharArray());
            else if (str.Contains(" ") && str.Contains(",")) strArray = str.Split(",".ToCharArray());
            else strArray = new string[] { str };

            string result = string.Empty;//定义一个空字符串

            foreach (string s in strArray)//循环处理数组里面每一个字符串
            {
                //result += System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(s) + " ";
                result += s.Substring(0, 1).ToUpper() + s.Substring(1) + " ";
                //.Substring(0, 1).ToUpper()把循环到的字符串第一个字母截取并转换为大写，并用s.Substring(1)得到循环到的字符串除第一个字符后的所有字符拼装到首字母后面。
            }
            return result;
        }
        protected string TransSync(string url, Dictionary<string, string> dic)
        {
            string result = "";
            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
            req.Method = "POST";
            req.ContentType = "application/x-www-form-urlencoded";

            StringBuilder builder = new StringBuilder();
            int i = 0;
            foreach (var item in dic)
            {
                if (i > 0)
                    builder.Append("&");
                if (item.Key == "q")
                {
                    builder.AppendFormat(item.Value);
                }
                else
                {
                    builder.AppendFormat("{0}={1}", item.Key, item.Value);
                }


                i++;
            }
            byte[] data = Encoding.UTF8.GetBytes(builder.ToString());
            req.ContentLength = data.Length;
            using (Stream reqStream = req.GetRequestStream())
            {
                reqStream.Write(data, 0, data.Length);
                reqStream.Close();
            }
            HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
            if (resp.ContentType.ToLower().Equals("audio/mp3"))
            {
                SaveBinaryFile(resp, "合成的音频存储路径");
            }
            else
            {
                Stream stream = resp.GetResponseStream();
                using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
                {
                    result = reader.ReadToEnd();
                }
            }
            return result;
        }
        private static bool SaveBinaryFile(WebResponse response, string FileName)
        {
            string FilePath = FileName + DateTime.Now.Millisecond.ToString() + ".mp3";
            bool Value = true;
            byte[] buffer = new byte[1024];

            try
            {
                if (File.Exists(FilePath))
                    File.Delete(FilePath);
                Stream outStream = File.Create(FilePath);
                Stream inStream = response.GetResponseStream();

                int l;
                do
                {
                    l = inStream.Read(buffer, 0, buffer.Length);
                    if (l > 0)
                        outStream.Write(buffer, 0, l);
                }
                while (l > 0);

                outStream.Close();
                inStream.Close();
            }
            catch
            {
                Value = false;
            }
            return Value;
        }
        protected static string ComputeHash(string input, HashAlgorithm algorithm)
        {
            byte[] inputBytes = Encoding.UTF8.GetBytes(input);
            byte[] hashedBytes = algorithm.ComputeHash(inputBytes);
            return BitConverter.ToString(hashedBytes).Replace("-", "");
        }
        protected static string Truncate(string q)
        {
            if (q == null)
            {
                return null;
            }
            int len = q.Length;
            return len <= 20 ? q : q.Substring(0, 10) + len + q.Substring(len - 10, 10);
        }
        public static DateTime GetBeijingTime()
        {
            WebRequest request = null;
            WebResponse response = null;
            WebHeaderCollection headerCollection = null;
            string datetime = string.Empty;
            try
            {
                request = WebRequest.Create("https://www.baidu.com");
                request.Timeout = 3000;
                request.Credentials = CredentialCache.DefaultCredentials;
                response = request.GetResponse();
                headerCollection = response.Headers;
                foreach (var h in headerCollection.AllKeys)
                {
                    if (h == "Date")
                    {
                        datetime = headerCollection[h];
                    }
                }
                return Convert.ToDateTime(datetime);
            }
            catch (Exception)
            {
                return DateTime.Now;
            }
            finally
            {
                if (request != null)
                {
                    request.Abort();
                }
                if (response != null)
                {
                    response.Close();
                }
                if (headerCollection != null)
                {
                    headerCollection.Clear();
                }
            }

        }



        public class TranslateResult
        {
            public string Query { get; set; }

            public string Translation { get; set; }

            public string Type { get; set; }

            public string VerifyResult { get; set; }

        }
    }
}