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;

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);
                    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 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();

                foreach (var item in _AirTicketReservations)
                {
                    if (item.FlightsDescription.Contains("\r\n"))
                    {
                        var spilitArr = Regex.Split(item.FlightsDescription, "\r\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[1].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)
                            {
                                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);

                AirGroupCostParameterView _AirgroupCostParameter = _mapper.Map<AirGroupCostParameterView>(_GroupCostParameter);

                var data = new
                {
                    DelegationInfo = _DelegationInfo,
                    AirTicketReservations = _AirTicketReservations,
                    AirGroupCostParameter = _AirgroupCostParameter
                };
                return result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
            }
            else
            {
                return result = new Result() { Code = -1, Msg = "暂无团组数据！" };
            }

        }

        public async Task<Result> AirTicketResSelect(AirTicketResDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                #region 团组下拉框


                List<Grp_GroupsTaskAssignment> grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == 85).ToList();

                string DiId = "";
                foreach (var item in grp_GroupsTaskAssignment)
                {
                    DiId += item.DIId + ",";
                }
                DiId = DiId.Substring(0, DiId.Length - 1);
                string sql = string.Format(@"select * from Grp_DelegationInfo where Id in({0}) and IsDel={1}", 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 = "查询失败！" };
                }
                List<GroupNameView> grp_NameView = new List<GroupNameView>();
                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
                var data = new
                {
                    TicketClass = _TicketClassa,
                    Payment = _Payment,
                    CardType = _CardType,
                    GroupName = grp_NameView,
                    AirTicketAgents = _AirTicketAgents
                };
                return result = new Result() { Code = 0, Msg = "查询成功！", Data = data };
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "程序错误" };
                throw;
            }
        }

        public async Task<Result> OpAirTicketRes(AirTicketResOpDto dto)
        {
            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 ClientName='{0}' and IsDel={1}"
                                                     , dto.AirTicketResOpData.ClientName, 0);
                    var DeleClient = await _sqlSugar.SqlQueryable<Grp_AirTicketReservations>(selectSql).FirstAsync();//查询是否存在
                    if (DeleClient != null)
                    {
                        return result = new Result() { Code = -1, Msg = "该客户已存在，请勿重复添加！" };
                    }
                    else//不存在，可添加
                    {

                        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);
                        //换算
                        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 = "1";
                                grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                            }
                        }
                        else
                        {
                            grp_CreditCard.DayRate = "1";
                            grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                        }

                        //判断是否超出成本
                        Grp_GroupCostParameter _GroupCostParameter = _sqlSugar.Queryable<Grp_GroupCostParameter>().First(a => a.DiId == grp_AirTicket.DIId && a.IsDel == 0);
                        if (grp_AirTicket.CType == 460)//经济舱
                        {
                            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 = DateTime.Now;
                                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 = new DateTime();
                                grp_CreditCard.IsPay = 0;
                            }

                        }
                        else if (grp_AirTicket.CType == 458)
                        {
                            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 = DateTime.Now;
                                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 = new DateTime();
                                grp_CreditCard.IsPay = 0;
                            }
                        }


                        grp_CreditCard.CId = id;
                        grp_CreditCard.CTable = 85;
                        grp_CreditCard.PayPercentage = 100;
                        //查询上一次付款信息
                        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 = new DateTime();// 上次付款时间 查询上一次
                        }

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

                        }
                        CommitTran();
                        return result = new Result() { Code = 0, Msg = "添加成功！" };
                        //C表操作
                    }
                    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,
                        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);

                        //换算
                        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 = "1";
                                grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                            }
                        }
                        else
                        {
                            grp_CreditCard.DayRate = "1";
                            grp_CreditCard.RMBPrice = grp_CreditCard.PayMoney;
                        }
                        //grp_CreditCard.PayMoney = grp_CreditCard.PayMoney;
                        //判断是否超出成本
                        Grp_GroupCostParameter _GroupCostParameter = _sqlSugar.Queryable<Grp_GroupCostParameter>().First(a => a.DiId == grp_AirTicket.DIId && a.IsDel == 0);
                        if (grp_AirTicket.CType == 460)//经济舱
                        {
                            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 = DateTime.Now;
                                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 = new DateTime();
                                grp_CreditCard.IsPay = 1;
                            }

                        }
                        else if (grp_AirTicket.CType == 458)
                        {
                            if (Convert.ToDecimal(_GroupCostParameter.GWCCB) * Convert.ToDecimal(grp_AirTicket.ClientNum) > grp_CreditCard.RMBPrice)
                            {
                                grp_CreditCard.ExceedBudget = 0;// 超出预算比例 换算
                                grp_CreditCard.IsAuditGM = 3;//3 为自动审核
                                grp_CreditCard.AuditGMOperate = 21;
                                grp_CreditCard.AuditGMDate = DateTime.Now;
                                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 = new DateTime();
                                grp_CreditCard.IsPay = 1;
                            }
                        }


                        grp_CreditCard.CId = id;
                        grp_CreditCard.CTable = 85;
                        grp_CreditCard.PayPercentage = 100;
                        //查询上一次付款信息
                        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 = new DateTime();// 上次付款时间 查询上一次
                        }
                        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
                        }).ExecuteCommandAsync();
                        if (CTable == 0)
                        {
                            RollbackTran();
                            return result = new Result() { Code = -1, Msg = "修改失败！" };

                        }
                        else
                        {
                            result = new Result() { Code = 0, Msg = "修改成功！" };
                        }
                    }
                    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 d.TourCode,d.TeamName,a.FlightsCity,a.FlightsCode,a.FlightsDescription,(select Name from Sys_SetData where Id=a.cType) as 'CTypeName',a.ClientName,a.ClientNum,
                                a.PrePrice,a.Price,(select Name from Sys_SetData where Id=c.PayDId) as 'PayType', case when 
                                c.OrbitalPrivateTransfer=0 then '公转' when c.OrbitalPrivateTransfer=1 then '私转' end as OrbitalPrivateTransfer,c.BankNo,c.CreateTime,
                                c.Payee,a.PriceDescription,(select Name from Sys_SetData where Id=a.Currency) as 'CurrencyStr',(select Name from Sys_SetData where Id=c.CTDId) as BankType
                                from Grp_AirTicketReservations a,Grp_CreditCardPayment c ,Grp_DelegationInfo d where  a.id=c.CId and d.Id=a.DIId  and a.isdel=0
                                and a.DIId={0} Order By a.CreateTime desc", dto.DiId, 0);
                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; }

        }
    }
}