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

using AutoMapper;

using MySqlX.XDevAPI.Relational;
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;

namespace OASystem.Infrastructure.Repositories.Resource
{
    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 = 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.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 = 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();// 上次付款时间 查询上一次
                        }
                        
                        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(AirTicketResDto 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;
            }
        }
       
    }
}