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

using AutoMapper;
using MathNet.Numerics.Statistics.Mcmc;
using MySqlX.XDevAPI.Common;
using OASystem.Domain;
using OASystem.Domain.Dtos.Financial;
using OASystem.Domain.Dtos.Groups;
using OASystem.Domain.Entities.Financial;
using OASystem.Domain.Entities.Groups;
using OASystem.Domain.ViewModels.Financial;
using OASystem.Domain.ViewModels.Groups;
using OASystem.Infrastructure.Tools;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Result = OASystem.Domain.Result;

namespace OASystem.Infrastructure.Repositories.Groups
{
    public class HotelPriceRepository : BaseRepository<Grp_HotelReservations, Grp_HotelReservations>
    {
        private readonly IMapper _mapper;
        private readonly Result _result;
        public HotelPriceRepository(SqlSugarClient sqlSugar, IMapper mapper) :
            base(sqlSugar)
        {
            this._mapper = mapper;
            _result = new Result() { Code = -1, Msg = "操作失败!" };
        }

        #region 保留

        

        /// <summary>
        /// 付款金额计算
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> HotelConversionAmounts(HotelReservationsCNYDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                HotelReservationsView reservationsView=new HotelReservationsView();
                if ((dto.CardPriceCurrency == dto.GovernmentRentCurrency || dto.GovernmentRent == 0)
                 && (dto.CardPriceCurrency == dto.CityTaxCurrency || dto.CityTax== 0))
                {
                    reservationsView.CurrencyId = dto.CardPriceCurrency;
                    reservationsView.Price=Convert.ToDecimal((dto.CardPrice+dto.GovernmentRent+dto.CityTax).ToString("F2"));
                    reservationsView.CurrencyName = _sqlSugar.Queryable<Sys_SetData>().First(a => a.Id == dto.CardPriceCurrency).Name;
                    return result = new Result() { Code = 0, Msg = "查询成功", Data = reservationsView };
                }
                else
                {
                    Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 76);
                    List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
                    decimal CardPrice = 0.00M;
                    decimal GovernmentRent = 0.00M;
                    decimal CityTax = 0.00M;
                    if (_TeamRate != null)
                    {
                        Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == dto.CardPriceCurrency);
                        if (_SetData != null)
                        {
                            if (_SetData.Name=="CNY")
                            {
                                CardPrice = dto.CardPrice;
                            }
                            else
                            {
                                currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                                CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
                                if (CurrencyRate != null) CardPrice = dto.CardPrice * Convert.ToDecimal(CurrencyRate.Rate);
                                else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };
                            }
                        }
                        else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };

                        Sys_SetData _SetData1 = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == dto.GovernmentRentCurrency);
                        if (_SetData1 != null)
                        {
                            if (_SetData1.Name == "CNY")
                            {
                                GovernmentRent = dto.GovernmentRent;
                            }
                            else {
                                currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                                CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData1.Name);
                                if (CurrencyRate != null) GovernmentRent = dto.GovernmentRent * Convert.ToDecimal(CurrencyRate.Rate);
                                else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };
                            }
                                
                        }
                        else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };

                        Sys_SetData _SetData2 = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == dto.CityTaxCurrency);
                        if (_SetData2 != null)
                        {
                            if (_SetData2.Name == "CNY")
                            {
                                CityTax = dto.CityTax;
                            }
                            else
                            {
                                currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                                CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData2.Name);
                                if (CurrencyRate != null) CityTax = dto.CityTax * Convert.ToDecimal(CurrencyRate.Rate);
                                else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };
                            }
                        }
                        else return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data = reservationsView };
                       
                        reservationsView.CurrencyId = 836;
                        reservationsView.Price = Convert.ToDecimal((CardPrice + GovernmentRent + CityTax).ToString("F2"));
                        reservationsView.CurrencyName ="CNY";

                        return result = new Result() { Code = 0, Msg = "查询成功", Data = reservationsView };
                    }
                    else
                    {
                       return result = new Result() { Code = -1, Msg = "暂未设置团组汇率，请前往设置！", Data= reservationsView };
                    }
                }
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }

       
        /// <summary>
        /// 根据团组id查询酒店数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> HotelReservationsByDiId(HotelReservationsByDiIdDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                string UserId = "";
                List<Grp_GroupsTaskAssignment> gtaUIdList = _sqlSugar.Queryable<Grp_GroupsTaskAssignment>().Where(a => a.DIId == dto.DiId && a.IsDel == 0 && a.CTId == 76).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 = string.Format(@"Where h.DiId={0} and h.IsDel={1} And h.CreateUserId in ({2})", dto.DiId, 0,UserId);
                
                int startIndex = (dto.PageIndex - 1) * dto.PageSize + 1;
                int endIndex = startIndex + dto.PageSize - 1;
                if (dto.PortType == 1)
                {
                    string sql = string.Format(@"select h.Id,s1.Name as GuestType,h.ReservationsNo,h.HotelName,h.CheckInDate,
                                                h.CheckOutDate,c.PayMoney,s.Name as PaymentCurrency,u.CnName as CreateUserName,c.IsAuditGM,h.CreateTime,h.Attachment
                                                From Grp_HotelReservations h 
                                                Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=76 and c.isdel=0
                                                left Join Sys_SetData s on  c.PaymentCurrency=s.Id  
                                                left Join Sys_SetData s1 on h.GTId=s1.Id 
                                                left Join Sys_Users u on u.Id=h.CreateUserId {0} order by c.IsAuditGM,c.PayPercentage desc", sqlWhere);
                    List<HotelReservationsByDiIdView> hotelDataList = _sqlSugar.SqlQueryable<HotelReservationsByDiIdView>(sql).ToList();
                    foreach (var item in hotelDataList)
                    {
                        if (item.IsAuditGM == 0) item.IsAuditGMStr = "未审核";
                        else if (item.IsAuditGM == 1) item.IsAuditGMStr = "已通过";
                        else if (item.IsAuditGM == 2) item.IsAuditGMStr = "未通过";
                        else if (item.IsAuditGM == 3) item.IsAuditGMStr = "自动审核";
                        if (!string.IsNullOrWhiteSpace(item.CheckInDate))
                        {
                            item.CheckInDate = DateTime.Parse(item.CheckInDate).ToString("yyyy-MM-dd");
                        }
                        if (!string.IsNullOrWhiteSpace(item.CheckOutDate))
                        {
                            item.CheckOutDate = DateTime.Parse(item.CheckOutDate).ToString("yyyy-MM-dd");
                        }

                        

                    }
                    return result = new Result() { Code = 0, Msg = "查询成功！", Data = hotelDataList };
                }
                else if (dto.PortType == 2 || dto.PortType == 3)
                {
                    string sql = string.Format(@"Select * From (	
                                                Select row_number() over (order by c.IsAuditGM,c.PayPercentage desc) as RowNumber,h.Id,s1.Name as GuestType,h.ReservationsNo,h.HotelName,h.CheckInDate,
                                                h.CheckOutDate,c.PayMoney,s.Name as PaymentCurrency,u.CnName as CreateUserName,c.IsAuditGM,h.CreateTime,h.Attachment
                                                 From Grp_HotelReservations h 
                                                Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=76 and c.isdel=0
                                                left Join Sys_SetData s on  c.PaymentCurrency=s.Id 
                                                left Join Sys_SetData s1 on h.GTId=s1.Id 
                                                left Join Sys_Users u on u.Id=h.CreateUserId   {0}
                                                ) temp Where RowNumber Between {1} and {2}", sqlWhere, startIndex, endIndex);

                    List<HotelReservationsByDiIdView> hotelDataList = _sqlSugar.SqlQueryable<HotelReservationsByDiIdView>(sql).ToList();
                    foreach (var item in hotelDataList)
                    {
                        if (item.IsAuditGM == 0) item.IsAuditGMStr = "未审核";
                        else if (item.IsAuditGM == 1) item.IsAuditGMStr = "已通过";
                        else if (item.IsAuditGM == 2) item.IsAuditGMStr = "未通过";
                        else if (item.IsAuditGM == 3) item.IsAuditGMStr = "自动审核";
                        if (!string.IsNullOrWhiteSpace(item.CheckInDate))
                        {
                            item.CheckInDate = DateTime.Parse(item.CheckInDate).ToString("yyyy-MM-dd");
                        }
                        if (!string.IsNullOrWhiteSpace(item.CheckOutDate))
                        {
                            item.CheckOutDate = DateTime.Parse(item.CheckOutDate).ToString("yyyy-MM-dd");
                        }
                    }
                    string CountSql = string.Format(@"Select COUNT(1) as Count From (	
                                                        Select h.Id,s1.Name as GuestType,h.ReservationsNo,h.HotelName,h.CheckInDate,
                                                        h.CheckOutDate,c.PayMoney,s.Name as PaymentCurrency,u.CnName as CreateUserName,c.IsAuditGM,h.CreateTime,h.Attachment
                                                        From Grp_HotelReservations h 
                                                        Join Grp_CreditCardPayment c on h.Id=c.CId and c.CTable=76 and c.isdel=0
                                                        left Join Sys_SetData s on  c.PaymentCurrency=s.Id 
                                                        left Join Sys_SetData s1 on h.GTId=s1.Id 
                                                        left Join Sys_Users u on u.Id=h.CreateUserId  {0}
                                                        ) temp", sqlWhere);
                    DataCount dataCount = _sqlSugar.SqlQueryable<DataCount>(CountSql).First();
                   
                    int count = dataCount.Count;
                    float totalPage = (float)count / dto.PageSize;//总页数
                    if (totalPage == 0) totalPage = 1;
                    else totalPage = (int)Math.Ceiling((double)totalPage);

                    ListViewBase<HotelReservationsByDiIdView> rst = new ListViewBase<HotelReservationsByDiIdView>();
                    rst.DataList = hotelDataList;
                    rst.DataCount = count;
                    rst.CurrPageIndex = dto.PageIndex;
                    rst.CurrPageSize = dto.PageSize;
                    return result = new Result() { Code = 0, Msg = "查询成功！", Data = rst };
                   
                }
                else
                {
                    return result = new Result() { Code = -2, Msg = "请传入PortType参数,1 Web 2 Android 3 IOS" };
                }
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }
        
        /// <summary>
        /// 根据酒店费用Id查询详细数据
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> HotelReservationsById(HotelReservationsByIdDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                Grp_HotelReservations hotelReservationsById = _sqlSugar.Queryable<Grp_HotelReservations>().First(a => a.IsDel == 0 && a.Id == dto.Id);
                HotelReservationsByIdView _hotelReservations = _mapper.Map<HotelReservationsByIdView>(hotelReservationsById);
                if (_hotelReservations!=null)
                {
                    Sys_SetData GTId = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == hotelReservationsById.GTId);
                    if (GTId != null) _hotelReservations.GtIdStr = GTId.Name;
                    if (!string.IsNullOrWhiteSpace(_hotelReservations.CheckInDate))
                    {
                        _hotelReservations.CheckInDate = Convert.ToDateTime(hotelReservationsById.CheckInDate).ToString("yyyy-MM-dd");
                    }
                    if (!string.IsNullOrWhiteSpace(_hotelReservations.CheckOutDate))
                    {
                        _hotelReservations.CheckOutDate = Convert.ToDateTime(hotelReservationsById.CheckOutDate).ToString("yyyy-MM-dd");
                    }
                    

                    Sys_SetData GovernmentRentCurrencyStr = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == hotelReservationsById.GovernmentRentCurrency);
                    if (GovernmentRentCurrencyStr != null) _hotelReservations.GovernmentRentCurrencyStr = GovernmentRentCurrencyStr.Name;

                    Sys_SetData CityTaxCurrencyStr = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == hotelReservationsById.CityTaxCurrency);
                    if (CityTaxCurrencyStr != null) _hotelReservations.CityTaxCurrencyStr = CityTaxCurrencyStr.Name;

                    if (_hotelReservations.CheckType == "") _hotelReservations.CheckTypeStr = "客人房";
                    else if (_hotelReservations.CheckType == "D") _hotelReservations.CheckTypeStr = "司机房";
                    else if (_hotelReservations.CheckType == "G") _hotelReservations.CheckTypeStr = "导游房";
                    else if (_hotelReservations.CheckType == "D&G") _hotelReservations.CheckTypeStr = "司机导游房";



                }
               
                string Sql = string.Format(@"select c.PayDId, s.Name asPayDIdStr,c.ConsumptionPatterns,c.ConsumptionDate,c.CTDId,
                                            s1.Name as CTDIdStr,c.BankNo,c.CardholderName,c.PayMoney,c.PaymentCurrency,s2.Name
                                            as PaymentCurrencyStr,c.DayRate,c.CompanyBankNo,c.OtherBankName,c.OtherSideNo,c.OtherSideName,
                                            c.IsAuditGM,c.Payee,c.RMBPrice,c.OrbitalPrivateTransfer,c.Remark from Grp_CreditCardPayment c
                                            left join Sys_SetData s on c.PayDId=s.Id
                                            left join Sys_SetData s1 on c.CTDId=s1.Id
                                            left join Sys_SetData s2 on c.PaymentCurrency=s2.Id
                                            where c.CId ={0} and c.IsDel = 0 and c.CTable = 76", dto.Id);
                Grp_CreditCardView _CreditCardPayment = _sqlSugar.SqlQueryable<Grp_CreditCardView>(Sql).First();
                if (_CreditCardPayment!=null)
                {
                    if(!string.IsNullOrWhiteSpace(_CreditCardPayment.ConsumptionDate)) _CreditCardPayment.ConsumptionDate=Convert.ToDateTime(_CreditCardPayment.ConsumptionDate).ToString("yyyy-MM-dd");
                }
                var data = new
                {
                    hotelReservations = _hotelReservations,
                    creditCardPayment = _CreditCardPayment,
                };
                return result = new Result() { Code = 0, Msg = "查询成功",Data= data };
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }
        /// <summary>
        /// 酒店页面下拉框等初始化
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> HotelReservationsInitialize(HotelReservationsDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                List<Grp_GroupsTaskAssignment> grp_GroupsTaskAssignment = Query<Grp_GroupsTaskAssignment>(a => a.IsDel == 0 && a.UId == dto.UserId && a.CTId == 76).ToList();

                string DiId = "0";
                foreach (var item in grp_GroupsTaskAssignment)
                {
                    DiId += item.DIId + ",";
                }
                if (DiId != "0")
                {
                    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> Delegations = _sqlSugar.SqlQueryable<Grp_DelegationInfo>(sql).ToList();
                List<ShareGroupInfoIIView> grp_Delegations = _mapper.Map<List<ShareGroupInfoIIView>>(Delegations);
                for (int i = 0;i< grp_Delegations.Count; i++)
                {
                    grp_Delegations[i].VisitDate = Delegations[i].VisitStartDate.ToString("yyyy-MM-dd")+"至"+ Delegations[i].VisitEndDate.ToString("yyyy-MM-dd");
                }
                //客人类型
                List<Sys_SetData> GuestType = _sqlSugar.Queryable<Sys_SetData>().Where(a=>a.IsDel==0 && a.STid==11).ToList();
                List<SetDataInfoView> _GuestType = _mapper.Map<List<SetDataInfoView>>(GuestType);

                //支付方式
                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> CurrencyList = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 66 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _CurrencyList = _mapper.Map<List<SetDataInfoView>>(CurrencyList);

                //卡类型
                List<Sys_SetData> BankCard = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 15 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _BankCard = _mapper.Map<List<SetDataInfoView>>(BankCard);

                //预订网站
                List<Sys_SetData> BookingWebsite = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 12 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _BookingWebsite = _mapper.Map<List<SetDataInfoView>>(BookingWebsite);
               
                if (dto.PortType==2 || dto.PortType==3)
                {
                    GeneralTeamRateInfoDto PostGroupTeamRatedto = new GeneralTeamRateInfoDto();
                    PostGroupTeamRatedto.DiId= dto.DiId;
                    PostGroupTeamRatedto.CTable = 76;
                    PostGroupTeamRatedto.PortType= dto.PortType;
                    Result teamRateDescAddCurrencyIdViews = PostGroupTeamRateByDiIdAndCTableId(PostGroupTeamRatedto);
                    List<TeamRateDescAddCurrencyIdView> Currency=new List<TeamRateDescAddCurrencyIdView>();
                    if (teamRateDescAddCurrencyIdViews.Code==0)
                    {
                        Currency = teamRateDescAddCurrencyIdViews.Data;
                    }
                    var data = new
                    {
                        GuestType = _GuestType,
                        Payment = _Payment,
                        CurrencyList = Currency,
                        BankCard = _BankCard,
                        BookingWebsite = _BookingWebsite,
                    };
                    return result = new Result() { Code = 0, Msg = "查询成功", Data = data };
                }
                else
                {
                    var data = new
                    {
                        Delegations = grp_Delegations,
                        GuestType = _GuestType,
                        Payment = _Payment,
                        CurrencyList = _CurrencyList,
                        BankCard = _BankCard,
                        BookingWebsite = _BookingWebsite,
                    };
                    return result = new Result() { Code = 0, Msg = "查询成功", Data = data };
                }
                
                
            }
            catch (Exception ex)
            {
               return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
        }

        /// <summary>
        /// 酒店操作
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<Result> OpHotelReservations(OpHotelReservationsData dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                BeginTran();
                int id = dto.Id;
                Grp_HotelReservations hotelPrice = _mapper.Map<Grp_HotelReservations>(dto);
                hotelPrice.IsCardPrice = hotelPrice.CardPrice!=0 ? 1:0;
                hotelPrice.CboOne=hotelPrice.SingleRoomCount !=0 ? 1 : 0;
                hotelPrice.CboTwo = hotelPrice.DoubleRoomCount != 0 ? 1 : 0;
                hotelPrice.CboThree = hotelPrice.SuiteRoomCount != 0 ? 1 : 0;
                hotelPrice.CboFour = hotelPrice.SuiteRoomCount != 0 ? 1 : 0;

                Grp_CreditCardPayment c = _mapper.Map<Grp_CreditCardPayment>(dto);
                c.Remark = dto.CRemark;
                c.PayPercentage = 100;
                c.CTable = 76;
                c.CId = id;
                c.IsAuditGM = 0;
                if (c.PayDId == 72)
                {
                    c.IsPay = 1;
                }
                c.RMBPrice = c.PayMoney;
                c.DayRate = 1;
                Grp_TeamRate _TeamRate = _sqlSugar.Queryable<Grp_TeamRate>().First(a => a.DiId == dto.DiId && a.IsDel == 0 && a.CTable == 76);
                List<CurrencyInfo> currencyInfos = new List<CurrencyInfo>();
                if (_TeamRate != null)
                {
                    Sys_SetData _SetData = _sqlSugar.Queryable<Sys_SetData>().First(a => a.IsDel == 0 && a.Id == c.PaymentCurrency);
                    if (_SetData != null)
                    {
                        currencyInfos = CommonFun.GetCurrencyChinaToList(_TeamRate.Remark);
                        CurrencyInfo CurrencyRate = currencyInfos.FirstOrDefault(a => a.CurrencyCode == _SetData.Name);
                        if (CurrencyRate != null)
                        {
                            c.RMBPrice = c.PayMoney * Convert.ToDecimal(CurrencyRate.Rate);
                            c.DayRate = CurrencyRate.Rate;
                        }
                    }
                }

                if (dto.Status == 1)//添加
                {
                    Grp_HotelReservations grp_Hotel = _sqlSugar.Queryable<Grp_HotelReservations>().First(a => a.IsDel == 0 && a.HotelName == dto.HotelName && a.GuestName == dto.GuestName &&
                    a.CheckInDate == dto.CheckInDate && a.CheckOutDate==dto.CheckOutDate && a.City==dto.City);
                    if (grp_Hotel != null)
                    {
                        return result = new Result() { Code = -1, Msg = "该笔费用已存在，请勿重复添加！" };
                    }
                    else
                    {
                        id = await AddAsyncReturnId(hotelPrice);
                        if (id != 0)
                        {
                            c.CId = id;
                            int cId = await _sqlSugar.Insertable(c).ExecuteReturnIdentityAsync();
                            if (cId != 0)
                            {
                                result = new Result() { Code = 0, Msg = "添加成功！" };

                            }
                            else
                            {
                                RollbackTran();
                                result = new Result() { Code = -1, Msg = "添加失败！" };
                            }
                        }
                        else
                        {
                            RollbackTran();
                            result = new Result() { Code = -1, Msg = "添加失败，请稍后重试！" };
                        }
                    }

                }
                else if (dto.Status == 2)//修改
                {
                    bool res = await UpdateAsync(a => a.Id == dto.Id, a => new Grp_HotelReservations
                    {
                        GTId=hotelPrice.GTId,
                        CheckNumber=hotelPrice.CheckNumber,
                        ReservationsWebsite=hotelPrice.ReservationsWebsite,
                        ReservationsNo=hotelPrice.ReservationsNo,
                        DetermineNo=hotelPrice.DetermineNo,
                        City=hotelPrice.City,
                        HotelName=hotelPrice.HotelName,
                        HotelAddress=hotelPrice.HotelAddress,
                        HotelTel=hotelPrice.HotelTel,
                        GuestName=hotelPrice.GuestName,
                        CheckInDate=hotelPrice.CheckInDate,
                        BudgetCurrency=hotelPrice.BudgetCurrency,
                        CheckOutDate=hotelPrice.CheckOutDate,
                        SingleRoomCount=hotelPrice.SingleRoomCount,
                        SingleRoomPrice=hotelPrice.SingleRoomPrice,
                        DoubleRoomCount=hotelPrice.DoubleRoomCount,
                        DoubleRoomPrice=hotelPrice.DoubleRoomPrice,
                        SuiteRoomCount=hotelPrice.SuiteRoomCount,
                        SuiteRoomPrice=hotelPrice.SuiteRoomPrice,
                        OtherRoomCount=hotelPrice.OtherRoomCount,
                        OtherRoomPrice=hotelPrice.OtherRoomPrice,
                        RoomExplanation=hotelPrice.RoomExplanation,
                        Attachment=hotelPrice.Attachment,
                        CardPrice=hotelPrice.CardPrice,
                        CardPriceCurrency=hotelPrice.CardPriceCurrency,
                        IsCardPrice =hotelPrice.IsCardPrice,
                        PredictSingleRoom=hotelPrice.PredictSingleRoom,
                        PredictDoubleRoom=hotelPrice.PredictDoubleRoom,
                        PredictSuiteRoom=hotelPrice.PredictSuiteRoom,
                        PredictOtherRoom=hotelPrice.PredictOtherRoom,
                        GovernmentRent=hotelPrice.GovernmentRent,
                        GovernmentRentCurrency=hotelPrice.GovernmentRentCurrency,
                        CityTax=hotelPrice.CityTax,
                        CityTaxCurrency=hotelPrice.CityTaxCurrency,
                        CheckType=hotelPrice.CheckType,
                        CboOne=hotelPrice.SingleRoomCount,
                        CboTwo=hotelPrice.DoubleRoomCount,
                        CboThree=hotelPrice.SuiteRoomCount,
                        CboFour=hotelPrice.SuiteRoomCount,
                        CreateUserId=hotelPrice.CreateUserId,
                        Remark=hotelPrice.Remark
                    });
                    if (res)
                    {
                        int CTable = await _sqlSugar.Updateable<Grp_CreditCardPayment>().Where(a => a.CId == hotelPrice.Id && a.CTable == 76).SetColumns(a => new Grp_CreditCardPayment
                        {
                            PayDId = dto.PayDId,
                            PayMoney = c.PayMoney,
                            PaymentCurrency = c.PaymentCurrency,
                            Payee = c.Payee,
                            OrbitalPrivateTransfer = c.OrbitalPrivateTransfer,
                            DayRate = c.DayRate,
                            RMBPrice = c.RMBPrice,
                            ConsumptionPatterns = c.ConsumptionPatterns,
                            ConsumptionDate = c.ConsumptionDate,
                            CTDId = c.CTDId,
                            CompanyBankNo = c.CompanyBankNo,
                            OtherBankName = c.OtherBankName,
                            OtherSideNo = c.OtherSideNo,
                            OtherSideName = c.OtherSideName,
                            BankNo = c.BankNo,
                            CardholderName = c.CardholderName,
                            Remark = c.Remark,

                        }).ExecuteCommandAsync();
                        if (CTable==0)
                        {
                            result = new Result() { Code = -1, Msg = "修改失败！" };
                            RollbackTran();
                        }
                        else
                        {
                            result = new Result() { Code = 0, Msg = "修改成功！" };
                        }
                        
                    }
                    else
                    {
                        RollbackTran();
                        result = new Result() { Code = -1, Msg = "修改失败，请稍后重试！" };
                    }
                }
                CommitTran();
            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
                throw;
            }
            return result;
        }
        public Result PostGroupTeamRateByDiIdAndCTableId(GeneralTeamRateInfoDto dto)
        {
            Result result = new Result() { Code = -2, Msg = "未知错误" };
            try
            {
                if (dto == null)
                {
                    return result = new Result() { Code = -2, Msg = "请输入参数" };
                }

                if (dto.DiId == 0)
                {
                    return result = new Result() { Code = -2, Msg = "请输入正确的团组Id!" };
                }
                if (dto.CTable == 0)
                {
                    return result = new Result() { Code = -2, Msg = "请输入正确的业务类型(CTable)Id!" };
                }


                if (dto.PortType == 1 || dto.PortType == 2 || dto.PortType == 3)
                {
                    string teamRateInfoSql = string.Format(@"Select sd.Name,tr.* From Grp_TeamRate tr 
                                                     Left Join Sys_SetData  sd On sd.IsDel=0 And sd.STid=16  And tr.CTable = sd.Id 
                                                     Where tr.IsDel = 0 And tr.DiId = {0} And tr.CTable = {1}", dto.DiId, dto.CTable);

                    var teamRateInfo =  _sqlSugar.SqlQueryable<TeamRateInfoView>(teamRateInfoSql).ToList();

                    #region 团组汇率

                    TeamRateModelGeneralView teamRateModels = new TeamRateModelGeneralView();


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

                    string sql = string.Format(@"select * from Sys_SetData where STid = {0} and isdel = 0", 66);
                    List<SetDataInfoView> DBdata = _sqlSugar.SqlQueryable<SetDataInfoView>(sql).ToList();
                    if (DBdata == null || DBdata.Count == 0)
                    {
                        return result = new Result() { Code = -2, Msg = "所有币种获取失败!" };
                    }

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

                    #endregion
                    List<TeamRateDescAddCurrencyIdView> teamRateDescViews = new List<TeamRateDescAddCurrencyIdView>();
                    foreach (TeamRateInfoView item in teamRateInfo)
                    {

                        #region 拆分remark里的汇率

                        if (item.Remark.Contains("|"))
                        {
                            string[] currencyArr = item.Remark.Split("|");
                            foreach (string currency in currencyArr)
                            {
                                string[] currency1 = currency.Split(":");
                                string[] currency2 = currency1[0].Split("(");

                                string currencyCode = currency2[1].Replace(")", "").TrimEnd();
                                SetDataInfoView dataInfoView = new SetDataInfoView();
                                dataInfoView = currencyDatas.Where(it => it.Name == currencyCode).FirstOrDefault();
                                int currencyId = currencyDatas.Where(it => it.Name == currencyCode).FirstOrDefault().Id;
                                TeamRateDescAddCurrencyIdView rateDescView = new TeamRateDescAddCurrencyIdView()
                                {
                                    CurrencyId = dataInfoView.Id,
                                    CurrencyCode = currencyCode,
                                    CurrencyName = currency2[0],
                                    Rate = decimal.Parse(currency1[1]),
                                };
                                teamRateDescViews.Add(rateDescView);
                            }
                        }

                        #endregion

                        if (teamRateDescViews.Count > 0)
                        {
                            teamRateDescViews = teamRateDescViews.OrderBy(it => it.CurrencyId).ToList();
                        }
                    }

                    #endregion
                    return result = new Result() { Code = 0, Msg = "操作成功!",Data= teamRateDescViews };
                    
                }
                else
                {
                    return result = new Result() { Code = -2, Msg = "请输入正确的端口号! 1 Web 2 Android 3 Ios;" };
                }



            }
            catch (Exception ex)
            {
                return result = new Result() { Code = -2, Msg = "未知错误" };
            }
        }

        #endregion

        /// <summary>
        /// 酒店预定费用
        /// Items By DiId
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> _ItemsByDiId(int portType,int diId)
        {

            string sql = string.Format(@"Select row_number() over(order by hr.CreateTime Desc) as Row_Number,
										 hr.Id,hr.DiId,sd1.Name As GuestType,hr.ReservationsNo,hr.HotelName,hr.CheckInDate,hr.CheckOutDate,
										 ccp.PayMoney, ccp.PaymentCurrency,sd2.Name PayCurrency,hr.CreateUserId,u.CnName As CreateUserName,
										 hr.CreateTime,ccp.IsAuditGM
										 From Grp_HotelReservations  hr
										 Inner Join Grp_CreditCardPayment ccp On hr.DiId = ccp.DIId And hr.Id = ccp.CId 
										 And ccp.CTable = 76
										 Left Join Sys_SetData sd1 On hr.GTId = sd1.Id
										 Left Join Sys_SetData sd2 On ccp.PaymentCurrency = sd2.Id
										 Left Join Sys_Users u On hr.CreateUserId = u.Id
										 Where hr.IsDel = 0 And ccp.IsDel = 0 And hr.DiId = {0} ", diId);

            if (portType == 1 || portType == 2 || portType == 3)
            {
                var hotelFeeData = await _sqlSugar.SqlQueryable<HotelReservationsItemsView>(sql).ToListAsync();

                _result.Code = 0;
                _result.Data = hotelFeeData;
                _result.Msg = "操作成功!";
            }
            else
            {
                _result.Msg = "请传入正确的PortType参数,1 Web 2 Android 3 IOS";
            }

            return _result;
        }

        /// <summary>
        /// 酒店预定
        /// basicsData Init
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<Result> _BasicsDataInit(HotelReservationBasicsDataInit dto)
        {
            try
            {
                if (dto.PortType == 1  || dto.PortType == 2 || dto.PortType == 3)
                {

                }
                else
                {
                    _result.Msg = "请传入正确的PortType参数,1 Web 2 Android 3 IOS";
                }

                List<Sys_SetData> _dataSouruce = await _sqlSugar.Queryable<Sys_SetData>().Where(a => a.IsDel == 0).ToListAsync();


                //客人类型
                List<Sys_SetData> GuestType = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.IsDel == 0 && a.STid == 11).ToList();
                List<SetDataInfoView> _GuestType = _mapper.Map<List<SetDataInfoView>>(GuestType);

                //支付方式
                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> CurrencyList = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 66 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _CurrencyList = _mapper.Map<List<SetDataInfoView>>(CurrencyList);

                //卡类型
                List<Sys_SetData> BankCard = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 15 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _BankCard = _mapper.Map<List<SetDataInfoView>>(BankCard);

                //预订网站
                List<Sys_SetData> BookingWebsite = _sqlSugar.Queryable<Sys_SetData>().Where(a => a.STid == 12 && a.IsDel == 0).ToList();
                List<SetDataInfoView> _BookingWebsite = _mapper.Map<List<SetDataInfoView>>(BookingWebsite);

                if (dto.PortType == 2 || dto.PortType == 3)
                {
                    GeneralTeamRateInfoDto PostGroupTeamRatedto = new GeneralTeamRateInfoDto();
                    PostGroupTeamRatedto.DiId = dto.DiId;
                    PostGroupTeamRatedto.CTable = 76;
                    PostGroupTeamRatedto.PortType = dto.PortType;
                    Result teamRateDescAddCurrencyIdViews = PostGroupTeamRateByDiIdAndCTableId(PostGroupTeamRatedto);
                    List<TeamRateDescAddCurrencyIdView> Currency = new List<TeamRateDescAddCurrencyIdView>();
                    if (teamRateDescAddCurrencyIdViews.Code == 0)
                    {
                        Currency = teamRateDescAddCurrencyIdViews.Data;
                    }
                    var data = new
                    {
                        GuestType = _GuestType,
                        Payment = _Payment,
                        CurrencyList = Currency,
                        BankCard = _BankCard,
                        BookingWebsite = _BookingWebsite,
                    };

                    _result.Code = 0;
                    _result.Data = data;
                    _result.Msg = "操作成功!";
                    return _result ;
                }
                else
                {
                    var data = new
                    {
                        GuestType = _GuestType,
                        Payment = _Payment,
                        CurrencyList = _CurrencyList,
                        BankCard = _BankCard,
                        BookingWebsite = _BookingWebsite,
                    };


                    _result.Code = 0;
                    _result.Data = data;
                    _result.Msg = "操作成功!";
                    return _result;
                }


            }
            catch (Exception ex)
            {
                return _result ;
            }
        }

    }
}